Database Administration Basics -1

Identify the database name, instance name, and size of the database blocks

SQL> select name from v$database; NAME
---------
TEST
1 row selected.

SQL> select instance,status from v$thread;
INSTANCE STATUS
---------------- ------
test OPEN
1 row selected.

SQL> select value,name from v$parameter where name like 'db_block_size';
VALUE NAME
-------------------- ---------------
8192 db_block_size
1 row selected.

List the name and size of the data files.

SQL> COLUMN NAME FORMAT A40
SQL> COLUMN BYTES FORMAT A10
SQL> SELECT NAME,BYTES/1024/1024 AS SIZEMB FROM V$DATAFILE;
NAME SIZEMB
---------------------------------------- ----------C:\ORACLE\ORADATA\TEST\SYSTEM01.DBF 370 370C:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF 200 200C:\ORACLE\ORADATA\TEST\CWMLITE01.DBF 20 20C:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF 120 120C:\ORACLE\ORADATA\TEST\INDX01.DBF 25 25C:\ORACLE\ORADATA\TEST\TOOLS01.DBF 98 .875
C:\ORACLE\ORADATA\TEST\USERS01.DBF 100 .5
C:\ORACLE\ORADATA\TEST\TEST1.DBF 1 C:\ORACLE\ORADATA\TEST\TEST2.DBF 1 C:\ORACLE\ORADATA\TEST\UNDO3.DBF 100
10 rows selected.

List the total number of tablespaces in the test database

SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
TABLESPACE_NAME
---------------
SYSTEM
UNDOTBS1
TEMP
CWMLITE
EXAMPLE
INDX
TOOLS
USERS
TEST
UNDO_3
10 rows selected.

Identify the datafile that makes up the UNDO_3 Tablespace

SQL> select file_name from dba_data_files where tablespace_name='UNDO_3';
FILE_NAME
----------------------------------------
C:\ORACLE\ORADATA\TEST\UNDO3.DBF
1 row selected.

Find the total freespace available in the database

SQL> select sum(bytes/1024/1024) "FREESPACE IN MB" from dba_free_space;
FREESPACE IN MB
----------------
415.875
1 row selected.

Find the total space available in the database

SQL> SELECT SUM(BYTES/1024/1024) "USEDSPACE IN MB" FROM DBA_SEGMENTS; USEDSPACE IN MB
---------------
795.71875
1 row selected.

Find the used space on a tablespace basis

SQL> SELECT TABLESPACE_NAME ,SUM(BYTES/1024/1024)"USED SPACE IN MB" 2FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME USED SPACE IN MB
--------------- ----------------
CWMLITE 17.4375
EXAMPLE 15.125
SYSTEM 362.3125
TEST .0625
TOOLS 91.75
UNDOTBS1 15.171875
UNDO_3 12.171875
USERS 281.6875
8 rows selected.

Oralce Files (Excerpts from Expert Oracle 10g Administration)
We will examine the eight file types that make up a database and instance. The files associated with an instance are simplyParameter files: These files tell the Oracle instance where to find the control files, and they also specify certain initialization parameters that define how big certain memory structures are, and so on. We will investigate the two options available for storing database parameter files.
Trace files: These are diagnostic files created by a serverprocess generally in response to some exceptional errorcondition. Alert file: This is similar to a trace file, but it containsinformation about “expected” events, and it also alerts theDBA in a single, centralized file of many database events The files that make up the database are Data files: These files are for the database; they hold yourtables, indexes, and all other segments. Temp files: These files are used for disk-based sorts andtemporary storage. Control files: These files tell you where the data files, tempfiles, and redo log files are, as well as other relevantmetadata about their state. Redo log files: These are your transaction logs. Password files: These files are used to authenticate usersperforming administrative activities over the network. We will not discuss these files in any detail.

For More details refer http://www.dbazine.com/oracle/or-articles/kyte4