Oracle DBA Quick Reference Queries
This document compiles a set of useful SQL queries and commands for Oracle Database Administrators (DBAs) to monitor, manage, and troubleshoot various aspects of an Oracle database.
RMAN Backup & Restore Status
These queries help monitor the status and details of RMAN backup and restore jobs.
1. RMAN Backup Job Details (Summary)
col START_TIME for a30
col END_TIME for a30
col STATUS format a30
col hrs format 999.99
set linesize 300
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
2. RMAN Backup Job Details (Size & Duration)
set linesize 150
select SESSION_KEY, INPUT_TYPE,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,STATUS,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,round(elapsed_seconds/60)+1 mins,round(input_bytes/1024/1024/1024) Size_Gigs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;
3. RMAN Backup Status (Disk Incrementals)
set lines 300
select to_char(start_time,'DD/MM/RR HH24:MI:SS') ,to_char(end_time,'DD/MM/RR HH24:MI:SS') ,Input_bytes/(1024*1024*1024),output_bytes/(1024*1024*1024) from v$rman_Status where OUTPUT_DEVICE_TYPE='DISK'and object_type='DB INCR' order by end_time desc;
4. RMAN Output Log
select output
from v$rman_output
where session_recid in (select session_recid from v$rman_status
where start_time > sysdate-2)
order by recid ;
5. RMAN Session Long Operations (Progress)
set linesize 126
column Pct_Complete format 99.99
column client_info format a25
column sid format 999
column MB_PER_S format 999.99
select s.client_info,
l.sid,
l.serial#,
l.sofar,
l.totalwork,
round (l.sofar / l.totalwork*100,2) "Pct_Complete",
aio.MB_PER_S,
aio.LONG_WAIT_PCT
from v$session_longops l,
v$session s,
(select sid,
serial,
100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",
sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"
from v$backup_async_io
group by sid, serial) aio
where aio.sid = s.sid
and aio.serial = s.serial#
and l.opname like 'RMAN%'
and l.opname not like '%aggregate%'
and l.totalwork != 0
and l.sofar <> l.totalwork
and s.sid = l.sid
and s.serial# = l.serial#
order by 1;
6. RMAN Throughput Calculation
TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' ||
ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input'
AND sofar != TOTALWORK
AND elapsed_seconds IS NOT NULL
),SUM(v.value /1024/1024)),2) || ' Meg/sec'
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
AND n.name = 'physical write total bytes'
AND v.sid = s.sid
AND v.inst_id = s.inst_id
AND s.program LIKE 'rman@%'
GROUP BY n.name
/
SET HEAD ON
Data Guard Archive Log Gap Status
Queries to check the synchronization status of a Data Guard standby database.
1. Archive Log Gap (Sequence Difference)
SELECT /*+ rule */ ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
2. Managed Standby Processes
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY;
Tablespace Status
Queries to monitor tablespace usage and properties.
1. Tablespace Free Space and Autoextend Info
SELECT d.tablespace_name as name,
NVL (dfs.BYTES, 0) AS freeSp,
NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100, 3), 0) AS avPct,
NVL (dfs.antall, 0) as chunks,
ddf.autoextend_flag as autoext,
NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize,
NVL (TRUNC ( (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.maxbytes)*100,3),0) as maxPct
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs,
(SELECT tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag FROM dba_data_files GROUP BY tablespace_name) ddf
WHERE d.tablespace_name = ddf.tablespace_name(+)
AND d.tablespace_name = dfs.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT LTRIM (d.tablespace_name) as name,
NVL (TRUNC (ddf.BYTES - NVL(dfs.BYTES, 0)), 0) AS freeSp,
NVL (TRUNC ((ddf.BYTES - NVL(dfs.BYTES, 0)) / ddf.BYTES * 100), 0) AS avPct,
DECODE (NVL (TRUNC (((ddf.BYTES - NVL(dfs.BYTES, 0)) / ddf.BYTES) * 100), 0), 0, 1, 100, 0,1) as chunks,
ddf.autoextend_flag as autoext,
NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize,
NVL (TRUNC ((NVL(dfs.BYTES, 0)) / (ddf.maxbytes)* 100,3),0) as maxPct
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag
FROM dba_temp_files GROUP BY tablespace_name) ddf,
(SELECT ss.tablespace_name, SUM ( ss.used_blocks * ts.BLOCKSIZE) BYTES, COUNT (1) antall
FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs
WHERE d.tablespace_name = ddf.tablespace_name(+)
AND d.tablespace_name = dfs.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY'
ORDER BY 1;
2. Tablespace Used/Free Space (Simplified)
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes)/1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Temp Tablespace Usage
Query to check the current usage of temporary tablespaces.
SET PAGESIZE 60
SET LINESIZE 300
SELECT
A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
v$sort_segment A,
(
SELECT
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM
v$tablespace B,
v$tempfile C
WHERE
B.ts#= C.ts#
GROUP BY
B.name,
C.block_size
) D
WHERE
A.tablespace_name = D.name
GROUP by
A.tablespace_name,
D.mb_total;
CREATE TABLESPACE / ADD DATAFILE / RESIZE DATAFILE
Commands for managing tablespaces and datafiles.
1. Create Tablespace
CREATE TABLESPACE "My_abs" DATAFILE
SIZE 1073741824
AUTOEXTEND ON NEXT 104857600 MAXSIZE 30720M
LOGGING ONLINE PERMANENT BLOCKSIZE 32768
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
2. Add Datafile to Tablespace
ALTER TABLESPACE ARCH_data_TS ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;
3. Resize Datafile
ALTER DATABASE DATAFILE '+DATA_DG/ABCP0009EC/DATAFILE/arch_lobs_ts.305.1003252759' resize 30G;
4. Get Tablespace DDL
set long 99999999
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
ASM Disk and Diskgroup Size
Queries to monitor Oracle Automatic Storage Management (ASM) disks and diskgroups.
1. ASM Disk Information
col path for a30
set linesize 300
select name,path,os_mb,header_Status from v$ASM_DISK;
2. ASM Disk Information (GV$)
select INST_ID,name,path from gv$ASM_DISk order by name,INST_ID;
3. ASM Diskgroup Size
select name,total_mb/1024,free_mb/1024 from v$ASM_DISKGROUP;
4. Candidate ASM Disks
select name,path,os_mb,header_Status from v$ASM_DISK where header_Status='CANDIDATE';
ASM Rebalance Status
Query to check the status of an ongoing ASM rebalance operation.
set linesize 300
select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM_OPERATION ;
DB Directory Name and Path
Query to list all database directory objects and their paths.
set linesize 300
col OWNER for a20
col DIRECTORY_NAME for a20
col DIRECTORY_PATH for a50
select * from dba_directories;
AWR Interval and Retention Change
Command to modify AWR snapshot settings.
-- Set AWR snapshot interval to 30 minutes and retention to 15 days (21600 minutes)
exec dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 21600) ;
AWR Manual Snapshot
Command to manually create an AWR snapshot.
EXEC dbms_workload_repository.create_snapshot;
Redo Log Member Size
Query to check the size of redo log members.
SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
GATHER_TABLE_STATS & GATHER_SCHEMA_STATS
Examples of gathering optimizer statistics for tables and schemas.
1. Gather Table Statistics
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'MyOwner',
TabName => 'Mytable',
Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE,
Method_Opt => 'FOR ALL COLUMNS SIZE AUTO',
Degree => 4,
Cascade => TRUE
-- ,No_Invalidate=> FALSE -- Uncomment and set to FALSE if you want to invalidate dependent cursors immediately
);
END;
/
2. Gather Schema Statistics
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'MyOwner'
, Granularity => 'DEFAULT'
, Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
, Method_Opt => 'FOR ALL COLUMNS SIZE AUTO'
, Degree => 4
, Cascade => TRUE
-- , No_Invalidate => FALSE -- Uncomment and set to FALSE if you want to invalidate dependent cursors immediately
);
END;
/
Table Size in Database / Schema Size
Queries to calculate the size of tables and schemas.
1. Table Size (including LOBs and Indexes)
set linesize 300
col owner for a30
col table_name for a30
select * from (
SELECT
owner,
table_name,
TRUNC(sum(bytes)/1024/1024) "SIZE MB"
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
--WHERE table_name='***********' -- Uncomment and specify table name to filter
GROUP BY table_name, owner
ORDER BY SUM(bytes) desc ) where rownum <= 10; -- Shows top 10 largest tables
2. Schema Size (in GB)
col owner for a30
col tablespace_name for a30
SELECT owner,SUM(BYTES)/1024/1024/1024 "GB" FROM DBA_SEGMENTS WHERE OWNER in ('ABC') group by owner;
3. Tablespace Size for a Schema (in GB)
SELECT tablespace_name, Sum(bytes)/1024/1024/1024 "GB" FROM dba_segments WHERE OWNER in ('ABC') group by tablespace_name;
Hidden Parameter Check
Query to check the current values of hidden (underscore prefixed) parameters.
SELECT x.ksppinm "Parameter",
Y.ksppstvl "Session Value",
Z.ksppstvl "Instance Value"
FROM x$ksppi X,
x$ksppcv Y,
x$ksppsv Z
WHERE x.indx = Y.indx
AND x.indx = z.indx
AND x.ksppinm LIKE '/_%' escape '/' -- Filters for hidden parameters
AND x.ksppinm='_optimizer_cost_based_transformation' -- Specify a particular hidden parameter
order by x.ksppinm;
Service Creation in RAC
Commands for creating and managing services in an Oracle Real Application Clusters (RAC) environment.
1. Add Service
srvctl add service -d MYDB -s My_SRV -r MYDB1,MYDB2
2. Start Service
srvctl start service -d MYDB -s My_SRV
3. Service Status
srvctl status service -d MYDB -s My_SRV
Blocking Session
Query to identify blocking sessions in the database.
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' Module=' || S1.MODULE || ') IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' Module=' || S2.MODULE || ') ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;
Kill Session for Users
PL/SQL block to kill all sessions for a specific user.
BEGIN
FOR r IN (select inst_id,sid,serial# from gv$session where username = 'Myuser')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid
|| ',' || r.serial# ||',@'|| r.inst_id || '''';
END LOOP;
END;
/
Dynamic SQL Query Generation
Examples of generating DDL/DML statements dynamically from dictionary views.
1. Generate Synonym Creation SQL
select 'create synonym '||owner||'.'||SYNONYM_NAME||' '||' FOR '|| TABLE_OWNER||'.'||TABLE_NAME||' ;' FROM DBA_SYNONYMS WHERE OWNER like 'ABC%' and table_owner='MY_ABC';
2. Generate Table Move to Tablespace SQL
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || tablespace_name || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2');
3. Generate LOB Segment Move SQL
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE LOB ('||COLUMN_NAME||') STORE AS SECUREFILE '||SEGMENT_NAME||CHR(10)||'(TABLESPACE My_lob);' SQL_STATEMENTS FROM DBA_LOBS WHERE OWNER like 'OWNER1%';
4. Generate Index Rebuild SQL
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE Mytablespace1 ' || ';' from DBA_INDEXES WHERE OWNER='OWNER1' and tablespace_name in ('Mytablespace') ;
Database Properties
Query to check database properties, especially NLS (National Language Support) settings.
col PROPERTY_VALUE for a50
set linesize 300
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like 'NLS%';
