Essential Oracle Database Diagnostic Queries and Administration Scripts

This reference provides a comprehensive set of SQL queries, shell commands, and Oracle utility invocations for diagnosing and managing Oracle Database environments, including single‑instance and RAC deployments.

Version and Instance Information

select * from gv$version;

select dbid, name, to_char(created,'YYYY-MM-DD') created, log_mode from gv$database;

select version, instance_name, to_char(startup_time,'YYYY-MM-DD') startup_time, status from gv$instance;

select instance_name, status from gv$instance;

Patch and Character Set

$ORACLE_HOME/OPatch/opatch lspatches

$ORACLE_HOME/OPatch/opatch lsinventory

set line 200
col value for a50
set pagesize 100
select * from nls_database_parameters;

Parameter Files

show parameter spfile

cat $ORACLE_HOME/dbs/init${INSTANCE_NAME}.ora

Tablespace Details and Usage

set line 300
col tablespace_name for a20
select tablespace_name, block_size, initial_extent, next_extent, max_size, status, contents, logging, extent_management, segment_space_management from dba_tablespaces;

set pagesize 9999
set linesize 132
col tablespace_name for a35
select a.tablespace_name, a.total_mb, f.free_mb,
       round(a.total_mb - f.free_mb,2) used_mb,
       round((f.free_mb / a.total_mb) * 100,2) "%_Free"
from (
  select tablespace_name, sum(bytes/1024/1024) total_mb from dba_data_files group by tablespace_name
) a,
(
  select tablespace_name, sum(bytes/1024/1024) free_mb from dba_free_space group by tablespace_name
) f
where a.tablespace_name = f.tablespace_name
order by "%_Free";

Datafile and UNDO Information

set line 300
col tablespace_name for a20
col file_name for a45
select tablespace_name, file_name, file_id, status,
       trunc(bytes/1024/1024/1024,2) FILE_GB,
       autoextensible,
       trunc(maxbytes/1024/1024/1024,2) MAX_GB
from dba_data_files
order by file_id;

select t.status, sum(t.blocks)*8/1024||'M' size_M from dba_undo_extents t group by t.status;

Rollback Segments, Temp Files, and Control Files

select * from v$rollname;

set line 150
col tablespace_name for a10
set pagesize 100
select owner, tablespace_name, segment_id, segment_name, status from dba_rollback_segs order by 2,3;

set line 300
col tablespace_name for a20
col file_name for a40
select tablespace_name, file_name, file_id, status,
       trunc(bytes/1024/1024/1024,2) FILE_GB,
       autoextensible,
       trunc(maxbytes/1024/1024/1024,2) MAX_GB
from dba_temp_files
order by file_id;

set pagesize 100
col name for a50
select status, name from v$controlfile;

Log Files and Archive Information

set line 300
col member for a50
select a.group#, THREAD#, b.member, a.members, a.status, a.sequence#, bytes/1024/1024 file_mb
from v$log a, v$logfile b where a.group# = b.group# order by 2,1;

archive log list;

set pagesize 100
select a_date, a_count from (
  select to_char(first_time,'YYYY-MM-DD') a_date, count(*) a_count from gv$log_history group by to_char(first_time,'YYYY-MM-DD') order by 1 desc
) where rownum <= 31;

Listener and Network Checks

lsnrctl status

lsnrctl status listener_scan1

ls -lrth $ORACLE_HOME/network/log/listener.log

cd /oracle/grid/diag/tnslsnr/CLUSTER_NAME/listener/trace

RAC and ASM Information

set line 300
col name for a10
col compatibility for a10
select group_number, name, block_size, total_mb, free_mb, (1-(free_mb/total_mb))*100 used, type, compatibility, voting_files from v$asm_diskgroup;

set line 300
col CREATE_DATE for a10
col name for a15
col path for a20
set pagesize 300
select GROUP_NUMBER, DISK_NUMBER, STATE, OS_MB, TOTAL_MB, FREE_MB, NAME, PATH, CREATE_DATE, VOTING_FILE from v$asm_disk order by 1,2;

Cluster Resources and Status

ocrcheck -config

ocrcheck

ocrcheck -local

crsctl query css votedisk

oifcfg getif

srvctl config database -d DB_NAME

cemutlo -n

crsctl check cluster -all

crsctl stat res -t

crsctl check crs

crsctl check has

olsnodes

Security, Roles, and User Checks

column OWNER format a10 heading 'OWNER'
column OBJECT_NAME format a80 heading 'OBJECT_NAME'
column OBJECT_TYPE format a40 heading 'OBJECT_TYPE'
select OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where object_name in ('DBMS_SUPPORT_INTERNAL','DBMS_SYSTEM_INTERNAL','DBMS_CORE_INTERNAL','DBMS_STANDARD_FUN9') or object_name like 'DBMS_SUPPORT_DBMONITOR%';

select grantee, granted_role from dba_role_privs where GRANTED_ROLE='DBA';

set line 300
col profile for a10
select * from dba_profiles where profile='DEFAULT' and resource_name in ('PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS');

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Size, Objects, and Segment Statistics

select trunc(sum(bytes)/1024/1024/1024,2) db_GB from dba_segments;

select owner, trunc(sum(bytes)/1024/1024,2) db_MB from dba_segments group by owner order by 1;

select owner, count(*) from dba_tables group by owner order by 1;

select owner, count(*) from dba_indexes group by owner order by 1;

select owner, count(*) from dba_views group by owner order by 1;

select owner, count(*) from dba_triggers group by owner order by 1;

select owner, count(*) from dba_procedures group by owner order by 1;

Backup Information (RMAN)

set line 300
set pagesize 150
col in_size for a10
col out_size for a10
col input_type for a10
col e for a20
col s for a20
select session_key, input_type, compression_ratio, INPUT_BYTES_DISPLAY in_size, output_bytes_display out_size,
       to_char(START_TIME,'YYYYMMDD HH24:MI:SS') S,
       to_char(END_TIME,'YYYYMMDD HH24:MI:SS') E,
       status
from v$rman_backup_job_details
where INPUT_TYPE='DB FULL'
order by S DESC;

SQL Execution Monitoring

set linesize 150
set pagesize 2000
select * from table(dbms_xplan.display_cursor('&SQL_ID'));

select * from v$session where status='ACTIVE' and username is not null and event not in ('SQL*Net message to client','SQL*Net message from client') order by LAST_CALL_ET;

Performance Metrics (QPS/TPS)

select value from v$sysmetric where metric_name='Executions Per Sec' and group_id = 2;

select value from v$sysmetric where metric_name='Executions Per Sec' and group_id = 3;

select (select VALUE from v$sysmetric where metric_name='User Commits Per Sec') +
       (select VALUE from v$sysmetric where metric_name='User Rollbacks Per Sec') as TPS
from dual;

Memory and CPU Usage (Linux)

# For CentOS 6 / SLES
usedMem1=`free -k|grep "cache:"|awk '{print $3}'`
shmem=`cat /proc/meminfo |grep -w Shmem|awk '{print $2}'`
usedMem=`expr $usedMem1 + $shmem`

# For RHEL 7 / newer kernels
usedMem1=`free -k|grep "Mem:"|awk '{print $3}'`
shmem=`cat /proc/meminfo |grep -w Shmem|awk '{print $2}'`
usedMem=`expr $usedMem1 + $shmem`

Startup and Shutdown Scripts

# db_startup.sh (example)
# Starts listener, starts the database, registers the instance
cat db_startup.sh

# db_shutdown.sh (example)
# Stops listener, shuts down the database, logs out
cat db_shutdown.sh

This cheat‑sheet enables Oracle DB administrators to quickly collect environment details, assess resource consumption, verify configuration, and diagnose issues across both single‑instance and RAC environments.