How to be familiar with a new Oracle database

Jephe Wu -

Objective: to take over a new database and issue some commands to know the basic things about this database


a. know what platform it is (workable on 10g)
select name, platform_id,platform_name from   v$database; 

note: use the following view result to get the endian table:
select PLATFORM_ID,platform_name,endian_format from v$transportable_platform;

b.  check if it's cluster database (RAC)
show parameter cluster_database;

c. check instance status
select * from v$instance;
note: you can find out instance_name, version, startup_time, status etc

d. check database status
select * from v$database;
select open_mode from v$database;

note: you can find out dbid, dbname, db_uniq_name, created_date, log_mode, db_role, platform_name etc

e. check datafile, logfile and controlfile name and location

select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;
select destination from v$archive_dest;
select name from v$tempfile;  temp file name

f. check archive log status
archive log list;
select log_mode from v$database;

g. check datafile, tempfile and online logfile size
select sum(bytes)/1024/1024 "MB" from dba_data_files;  Size of datafiles in MB
select sum(bytes)/1024/1024 "MB" from dba_temp_files;   Size of tempfiles in MB
select sum(bytes)/1024/1024 from v$log;  Size of redo logs in MB (Not accounting for mirrored redolog files)

 select sum(bytes)/(1024*1024) from dba_segments;  

To get the used-up space of your datafiles 

Total Size of the database  (
Also accounting for controlfiles and mirrored redolog files)
select a.data_size+b.temp_size+c.redo_size+d.cont_size "total_size"
from ( select sum(bytes) data_size
       from dba_data_files ) a,
     ( select nvl(sum(bytes),0) temp_size
       from dba_temp_files ) b,
     ( select sum(bytes) redo_size
       from sys.v_$logfile lf, sys.v_$log l
       where = c,
     ( select sum(block_size*file_size_blks) cont_size
       from v$controlfile ) d;

h. check using spfile or pfile
show parameter spfile;

i. check dataguard status
select database_role from v$database;
 (primary or standby)

dgmgrl / as sysdba
show configuration

# check archive log destination settings.
select destination, error from v$archive_dest;

j. check characterset
select * from nls_database_parameters;
select * from v$nls_parameters;

commonly used commands:
1. alter system register
2. alter system checkpoint
3. alter system switch logfile;

1. alter system syntax