15 DB Monitoring SQL Scripts for DBAs

prompt   1. List of active sessions in the database 


set echo off  
set linesize 200
set head on  
set feedback on  
col sid head "Sid" form 9999 trunc  
col serial# form 99999 trunc head "Ser#"  col username form a8 trunc  col osuser form a7 trunc  
col machine form a20 trunc head "Client|Machine"  col program form a15 trunc head "Client|Program"  
col login form a11  col "last call" form 9999999 trunc head "Last Call|In Secs"  
col status form a6 trunc
col kill for a17  
select 
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, con_id,substr(username,1,10) username,substr(osuser,1,10) osuser,  substr(program||module,1,15) program,substr(machine,1,22) machine,  
to_char(logon_time,'ddMon hh24:mi') login,  last_call_et "last call",status  ,con_id ,sql_id
from gv$session 
where status='ACTIVE'  
order by 1  
/



SET LINESIZE 200
COLUMN blocker_info FORMAT a35 HEADING "Blocker (Inst:Sid:Serial)"
COLUMN waiter_info  FORMAT a35 HEADING "Waiter (Inst:Sid:Serial)"
COLUMN sql_id       FORMAT a15 HEADING "Waiter SQL ID"
COLUMN event        FORMAT a30 HEADING "Wait Event" TRUNC

SELECT 
    b.inst_id || ':' || b.sid || ':' || b.serial# AS blocker_info,
    s.inst_id || ':' || s.sid || ':' || s.serial# AS waiter_info,
    s.sql_id,
    s.seconds_in_wait,
    s.event
FROM gv$session s
JOIN gv$session b ON s.blocking_session = b.sid 
                 AND s.blocking_instance = b.inst_id
WHERE s.blocking_session IS NOT NULL;
/




prompt  2. Current tablespace usage & ASM

-- ASM

SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 1000 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name        FORMAT a12           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a45           HEAD 'Path'
COLUMN disk_file_name         FORMAT a12           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a12           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL ""              OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM v$asm_diskgroup a ,v$asm_disk b 
where a.group_number (+) =b.group_number 
ORDER BY a.name;




set feedback off  set pagesize 200
set linesize 2000  
set head on  COLUMN Tablespace format a25 heading 'Tablespace Name'  
COLUMN autoextensible format a11 heading 'AutoExtend'  
COLUMN files_in_tablespace format 999 heading 'Files'  
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'  
COLUMN total_used_space format 99999999 heading 'UsedSpace'  
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'  
COLUMN total_used_pct format 9999 heading '%Used'  
COLUMN total_free_pct format 9999 heading '%Free'  
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'  
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'  
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'  

WITH tbs_auto AS  (SELECT DISTINCT tablespace_name, autoextensible  
FROM dba_data_files  WHERE autoextensible = 'YES'),  files AS  (SELECT tablespace_name, COUNT (*) tbs_files,  SUM (BYTES/1024/1024) total_tbs_bytes  
FROM dba_data_files  GROUP BY tablespace_name),  fragments AS  (SELECT tablespace_name, COUNT (*) tbs_fragments,  SUM (BYTES)/1024/1024 total_tbs_free_bytes,  MAX (BYTES)/1024/1024 max_free_chunk_bytes  
FROM dba_free_space 
GROUP BY tablespace_name),  AUTOEXTEND AS  (SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs  
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow  FROM dba_data_files  WHERE autoextensible = 'YES'  GROUP BY tablespace_name  
UNION  
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow  FROM dba_data_files  WHERE autoextensible = 'NO'  GROUP BY tablespace_name)  
GROUP BY tablespace_name)  SELECT c.instance_name,a.tablespace_name Tablespace,  CASE tbs_auto.autoextensible  WHEN 'YES'  THEN 'YES'  ELSE 'NO'  END AS autoextensible,  files.tbs_files files_in_tablespace,  
files.total_tbs_bytes total_tablespace_space,  (files.total_tbs_bytes - fragments.total_tbs_free_bytes  ) total_used_space,  
fragments.total_tbs_free_bytes total_tablespace_free_space,  round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)  / files.total_tbs_bytes  )  * 100  )) total_used_pct,  
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100  )) total_free_pct  
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto  
WHERE a.tablespace_name = files.tablespace_name  
AND a.tablespace_name = fragments.tablespace_name  
AND a.tablespace_name = AUTOEXTEND.tablespace_name  
AND a.tablespace_name = tbs_auto.tablespace_name(+)  
order by total_free_pct;  


prompt  3. Find the blocking session details: 

SELECT  s.inst_id,  s.blocking_session,  s.sid,  s.serial#,  s.seconds_in_wait  FROM  gv$session s  
WHERE  blocking_session IS NOT NULL;



prompt  4. Monitor TEMP tablespace usage:

select a.tablespace_name tablespace,  d.TEMP_TOTAL_MB,  sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,  d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB  from v$sort_segment a,
  (  select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB  
  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.TEMP_TOTAL_MB;


prompt  5. Long Running Queries (Progress Monitor)


SET LINESIZE 300 PAGESIZE 100
COLUMN opname          FORMAT a25      HEADING "Operation"
COLUMN progress_pct    FORMAT a10      HEADING "Progress"
COLUMN start_time      FORMAT a15      HEADING "Started"
COLUMN time_remaining  FORMAT 999,999  HEADING "Remain(Sec)"
COLUMN sofar_total     FORMAT a20      HEADING "Work (Done/Total)"

SELECT 
    sid,
    inst_id,
    opname,
    sofar || ' / ' || totalwork AS sofar_total,
    ROUND((sofar/NULLIF(totalwork,0)) * 100, 2) || '%' AS progress_pct,
    TO_CHAR(start_time, 'DD-Mon HH24:MI') AS start_time,
    time_remaining
FROM gv$session_longops
WHERE totalwork <> sofar
  AND totalwork > 0
ORDER BY start_time DESC;
/

prompt  6. Get OS Process ID (SPID) from SID



SET LINESIZE 150 PAGESIZE 100
SET VERIFY OFF
COLUMN username FORMAT a15   HEADING "DB User"
COLUMN osuser   FORMAT a12   HEADING "OS User"
COLUMN spid     FORMAT a10   HEADING "OS PID"
COLUMN program  FORMAT a25   HEADING "Program" TRUNC
COLUMN machine  FORMAT a20   HEADING "Machine" TRUNC

SELECT 
    p.spid, 
    s.username, 
    s.osuser,
    s.program, 
    s.machine, 
    s.sid, 
    s.serial#, 
    s.status 
FROM gv$session s
JOIN gv$process p ON s.paddr = p.addr 
AND s.inst_id = p.inst_id
WHERE s.sid = &sid
;
/


prompt   7. Get sid from os spid: 

col sid format 999999  
col username format a20  
col osuser format a15  
select b.spid,a.sid, a.serial#,a.username, a.osuser  from v$session a, v$process b  where a.paddr= b.addr  and b.spid='&spid'  
order by b.spid;  





prompt  8.undo tablespace total, free and used space


SET LINESIZE 300 PAGESIZE 100
COLUMN tablespace_name FORMAT a25       HEADING "Tablespace Name"
COLUMN sizemb          FORMAT 999,999   HEADING "Total Size (MB)"
COLUMN usagemb         FORMAT 999,999   HEADING "Used (MB)"
COLUMN freemb          FORMAT 999,999   HEADING "Free (MB)"
COLUMN pct_used        FORMAT 990.9     HEADING "% Used"

SELECT 
    a.tablespace_name,
    a.sizemb,
    NVL(b.usagemb, 0) AS usagemb,
    a.sizemb - NVL(b.usagemb, 0) AS freemb,
    ROUND((NVL(b.usagemb, 0) / a.sizemb) * 100, 1) AS pct_used
FROM (
    -- Get total size of all UNDO tablespaces
    SELECT 
        df.tablespace_name, 
        SUM(df.bytes) / 1024 / 1024 AS sizemb
    FROM dba_data_files df
    JOIN dba_tablespaces ts ON df.tablespace_name = ts.tablespace_name
    WHERE ts.contents = 'UNDO'
    GROUP BY df.tablespace_name
) a
LEFT JOIN (
    -- Get usage from Active and Unexpired extents only
    SELECT 
        tablespace_name, 
        SUM(bytes) / 1024 / 1024 AS usagemb
    FROM dba_undo_extents
    WHERE status IN ('ACTIVE', 'UNEXPIRED')
    GROUP BY tablespace_name
) b ON a.tablespace_name = b.tablespace_name;
/




prompt  9. Get SQL Text of a Session

define inst_id=''
SET LINESIZE 150
SET PAGESIZE 100
SET VERIFY OFF
COLUMN sql_text FORMAT a100 HEADING "SQL Text"

SELECT 
    piece,
    sql_text
FROM gv$sqltext
WHERE (sql_id, inst_id) = (
    SELECT sql_id, inst_id 
    FROM gv$session 
    WHERE sid = &sid 
   --   AND inst_id = &inst_id
)
ORDER BY piece
;
/



prompt  10. Database Locks and Objects

SET LINESIZE 160
SET PAGESIZE 100
SET FEEDBACK ON

COLUMN sid             FORMAT 999999  HEADING "Sid"
COLUMN object_name     FORMAT a30     HEADING "Table|Locked"
COLUMN oracle_username FORMAT a12     HEADING "Oracle|User" TRUNC
COLUMN os_user_name    FORMAT a12     HEADING "OS User" TRUNC
COLUMN process         FORMAT a12     HEADING "Client|Process"
COLUMN mode_held       FORMAT a20     HEADING "Lock Mode"
COLUMN status          FORMAT a8      HEADING "Status"

SELECT 
 --   lo.inst_id,
	--do.con_id,
	''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.con_id,
    --lo.session_id AS sid,
    s.status,
    lo.oracle_username,
    lo.os_user_name,
    lo.process,
    do.object_name,
    DECODE(lo.locked_mode,
        0, 'None',
        1, 'Null (NULL)',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share (S)',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive (X)',
        TO_CHAR(lo.locked_mode)) AS mode_held
FROM gv$locked_object lo
JOIN cdb_objects do ON lo.object_id = do.object_id
JOIN gv$session s ON lo.session_id = s.sid AND lo.inst_id = s.inst_id
ORDER BY lo.inst_id, lo.session_id, do.object_name;
/





--- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.con_id,


prompt  11. High Redo Generating Sessions


SET LINESIZE 200
SET PAGESIZE 100
COLUMN name        FORMAT a10      HEADING "Stat Name"
COLUMN redo_mb     FORMAT 999,999.99 HEADING "Redo (MB)"
COLUMN username    FORMAT a15      HEADING "User"
COLUMN program     FORMAT a20      HEADING "Program" TRUNC
COLUMN module      FORMAT a20      HEADING "Module" TRUNC
COLUMN status      FORMAT a10      HEADING "Status"

SELECT 
    s.sid,
	s.con_id,
    sn.serial#,
    n.name, 
    ROUND(s.value/1024/1024, 2) AS redo_mb, 
    sn.username,
    sn.status,
    sn.program,
    sn.module,
    sn.sql_id
FROM gv$sesstat s 
JOIN gv$statname n ON n.statistic# = s.statistic#  
JOIN gv$session sn ON sn.sid = s.sid AND sn.inst_id = s.inst_id
WHERE n.name = 'redo size' 
  AND s.value > 0
ORDER BY redo_mb DESC;
/




prompt  12. Sessions with Active Undo Transactions

SET LINESIZE 180
SET PAGESIZE 100
COLUMN username         FORMAT a15      HEADING "User"
COLUMN used_undo_record FORMAT 999,999,999 HEADING "Undo Records"
COLUMN used_undo_mb     FORMAT 999,999.99 HEADING "Undo MB"
COLUMN status           FORMAT a10      HEADING "Status"
COLUMN start_time       FORMAT a20      HEADING "TX Start Time"

SELECT 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.con_id,
  --  s.sid,
   -- s.serial#,
    s.username,
    s.status,
    t.start_time,
    t.used_urec AS used_undo_record,
    ROUND((t.used_ublk * p.value) / 1024 / 1024, 2) AS used_undo_mb
FROM gv$session s
JOIN gv$transaction t ON s.saddr = t.ses_addr AND s.inst_id = t.inst_id
JOIN v$parameter p ON p.name = 'db_block_size'
ORDER BY used_undo_mb DESC;
/





prompt  13. TEMP Usage by Session (Formatted)

SET LINESIZE 200
SET PAGESIZE 100
COLUMN temp_size_mb FORMAT 999,999.99 HEADING "Temp Used(MB)"
COLUMN tablespace   FORMAT a15        HEADING "TS Name"
COLUMN sid_serial   FORMAT a15        HEADING "Sid,Serial"
COLUMN username     FORMAT a15        HEADING "User"
COLUMN program      FORMAT a20        HEADING "Program" TRUNC
COLUMN sql_id       FORMAT a15        HEADING "Current SQL"

SELECT 
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill, a.con_id,
    b.tablespace,  
    ROUND(((b.blocks * p.value)/1024/1024),2) AS temp_size_mb,  
    -- a.inst_id AS instance,  
  --  a.sid || ',' || s.serial# AS sid_serial,  
    NVL(a.username, '(oracle)') AS username,  
    a.program,  
    a.status,  
    a.sql_id  
FROM gv$session a
JOIN gv$sort_usage b ON a.saddr = b.session_addr AND a.inst_id = b.inst_id
JOIN gv$parameter p ON p.name = 'db_block_size' AND a.inst_id = p.inst_id
JOIN gv$session s ON a.sid = s.sid AND a.inst_id = s.inst_id
ORDER BY temp_size_mb DESC;
/


prompt  14. Rollback & Recovery Progress Monitor

SET LINESIZE 150
SET PAGESIZE 100
COLUMN state            FORMAT a15      HEADING "State"
COLUMN undoblocksdone   FORMAT 999,999,999 HEADING "Blocks Done"
COLUMN undoblockstotal  FORMAT 999,999,999 HEADING "Total Blocks"
COLUMN percent_complete FORMAT a15      HEADING "Progress"
COLUMN blocks_left      FORMAT 999,999,999 HEADING "Remaining"

SELECT 
INST_ID,
CON_ID,
    state,
    undoblocksdone,
    undoblockstotal,
    undoblockstotal - undoblocksdone AS blocks_left,
    LPAD(ROUND((undoblocksdone / NULLIF(undoblockstotal, 0)) * 100, 2) || '%', 8) AS percent_complete
FROM gv$fast_start_transactions;
/



prompt  15. Top Queries by Average Elapsed Time (Last 1 Hour)


SET LINESIZE 250
SET PAGESIZE 100
COLUMN username         FORMAT a15      HEADING "Schema"
COLUMN avg_elapsed_sec  FORMAT 999,990.99 HEADING "Avg Elap(s)"
COLUMN executions       FORMAT 999,999  HEADING "Execs"
COLUMN sql_id           FORMAT a15      HEADING "SQL ID"
COLUMN module           FORMAT a20      HEADING "Module" TRUNC
COLUMN sql_fulltext     FORMAT a60      HEADING "SQL Text" TRUNC




SET LONG 20000 LINESIZE 400
SET PAGESIZE 100
COLUMN username         FORMAT a15      HEADING "Schema"
COLUMN avg_elapsed_sec  FORMAT 999,990.99 HEADING "Avg Elap(s)"
COLUMN sql_id           FORMAT a15      HEADING "SQL ID"
COLUMN module           FORMAT a20      HEADING "Module" TRUNC
COLUMN sql_fulltext     FORMAT a60      HEADING "SQL Text" TRUNC

SELECT 
''''||sess.sid ||','|| sess.serial#||',@'||sess.inst_id ||'''' kill, sess.con_id,
sql.parsing_schema_name AS username,
    sess.module,  -- Fixed the ambiguity here
    sql.sql_id,
    sql.plan_hash_value,
    sql.executions,
    ROUND(sql.elapsed_time / NULLIF(sql.executions, 0) / 1000000, 2) AS avg_elapsed_sec,
    ROUND(sql.cpu_time / NULLIF(sql.executions, 0) / 1000000, 2) AS avg_cpu_sec,
    sql.rows_processed,
    TO_CHAR(sql.last_active_time, 'DD/MM/YY HH24:MI:SS') AS last_active,
	event,
    sql.sql_fulltext
FROM gv$sql sql
JOIN gv$session sess 
  ON sql.sql_id = sess.sql_id 
 AND sql.inst_id = sess.inst_id
WHERE sql.last_active_time > SYSDATE - 1/24
  AND sql.executions > 0
 -- AND ROUND(sql.elapsed_time / NULLIF(sql.executions, 0) / 1000000, 2) > 5
ORDER BY avg_elapsed_sec DESC
/