Friday, 22 March 2013



Server identification:

Host name where the instance is running

select host_name 
from v$instance;
You can also obtain this information by running the following from bash:

uname –n

Operating system platform

select platform_name from v$database –-(10g)

Similar information is returned if you run uname –s

File Information

Control files

select name 
from v$controlfile;

Location of the database control files. The init.ora parameter control_files also contains this information.


select file_name 
from Dba_data_files;

Location of the database datafiles

Temp files

select file_name 
from Dba_temp_files;

Location of database temporary files

Log files

select member 
from v$logfile;

Location of redo logs

Archived logs

select name 
from v$archived_log;

Location of archived redo logs. The init.ora parameters log_archive_dest_n also contain this information. This query will not return results if your database is not in Archivelog mod

Flash recovery area

select name 
from v$recovery_file_dest;

The directory being used in an Oracle 10g installation for the flash recovery area. The init.ora parameter db_recovery_file_dest also contains this information.

Other points of access on the file system indicated by parameters

select * 
from v$parameter
where value like '%/%'
value like '%/%';

The results of this query can vary significantly, depending on your installation and version of Oracle Database. Parameters that may be returned include:
background_dump_dest user_dump_dest 

Programmatic access to the file system

select directory_path from dba_directories;

The Oracle UTL_FILE_DIR parameter and DIRECTORY database objects can be used to access files that are not a part of standard database functioning.

Process Information

Session Processes

select p.spid, s.username, s.program
from v$process p, v$session s
where p.addr=s.paddr order by 2, 3, 1;

The spid can be correlated with ps –ef results to compare the information available within the database with OS information for a given process.

Processes related to parallelism

select slave_name, status 
from v$PQ_SLAVE;

Many aspects of Oracle Database, such as loading, querying, object creation, recovery, and replication, can take advantage of parallelism to speed up activities that can be broken down. The parameter parallel_threads_per_cpu sets an instance’s default degree of parallelism.

Memory Information

Program Global Area

select * from V$PGASTAT;

The parameter pga_aggregate_target is used to configure memory for all dedicated server connections.
Linux utilities, including vmstat and top, can be used to monitor memory usage.

System Global Area

select * from v$sga;

The parameters SGA_MAX_SIZE and SGA_TARGET are used to configure dynamic memory allocation features of Oracle Database 10 g. Other parameters can be used to manually allocate memory for particular purposes.
Again, various Linux utilities are available to monitor memory allocation.

No comments:

Post a Comment