Script: library_cache.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays library cache statistics.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @library_cache
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT a.namespace "Name Space",
           a.gets "Get Requests",
           a.gethits "Get Hits",
           Round(a.gethitratio,2) "Get Ratio",
           a.pins "Pin Requests",
           a.pinhits "Pin Hits",
           Round(a.pinhitratio,2) "Pin Ratio",
           a.reloads "Reloads",
           a.invalidations "Invalidations"
    FROM   v$librarycache a
    ORDER BY 1;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: latches.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all current latches.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @latches
       -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    SELECT l.latch#,
           l.name,
           l.gets,
           l.misses,
           l.sleeps,
           l.immediate_gets,
           l.immediate_misses,
           l.spin_gets
    FROM   v$latch l
    ORDER BY l.name;

Read More

Script: latch_holders.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all current latch holders.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @latch_holders
        -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    SELECT l.name "Latch Name",
           lh.pid "PID",
           lh.sid "SID",
           l.gets "Gets (Wait)",
           l.misses "Misses (Wait)",
           l.sleeps "Sleeps (Wait)",
           l.immediate_gets "Gets (No Wait)",
           l.immediate_misses "Misses (Wait)"
    FROM   v$latch l,
           v$latchholder lh
    WHERE  l.addr = lh.laddr
    ORDER BY l.name;

Read More

Script: latch_hit_ratios.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays current latch hit ratios.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @latch_hit_ratios
        -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    COLUMN latch_hit_ratio FORMAT 990.00
    
    SELECT l.name,
           l.gets,
           l.misses,
           ((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
    FROM   v$latch l
    WHERE  l.gets   != 0
    UNION
    SELECT l.name,
           l.gets,
           l.misses,
           100 AS latch_hit_ratio
    FROM   v$latch l
    WHERE  l.gets   = 0
    ORDER BY 4 DESC;

Read More

Script: jobs_running.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all jobs currently running.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @jobs_running
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT a.job "Job",
           a.sid,
           a.failures "Failures",      
           Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",     
           Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"            
    FROM   dba_jobs_running a;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: jobs.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all scheduled jobs.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @jobs
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 1000 PAGESIZE 1000

    COLUMN log_user FORMAT A15
    COLUMN priv_user FORMAT A15
    COLUMN schema_user FORMAT A15
    COLUMN interval FORMAT A40
    COLUMN what FORMAT A50
    COLUMN nls_env FORMAT A50
    COLUMN misc_env FORMAT A50

    SELECT a.job,           
           a.log_user,      
           a.priv_user,    
           a.schema_user,   
           To_Char(a.last_date,'DD-MON-YYYY HH24:MI:SS') AS last_date,     
           --To_Char(a.this_date,'DD-MON-YYYY HH24:MI:SS') AS this_date,     
           To_Char(a.next_date,'DD-MON-YYYY HH24:MI:SS') AS next_date,     
           a.broken,        
           a.interval,      
           a.failures,      
           a.what,
           a.total_time,    
           a.nls_env,       
           a.misc_env         
    FROM   dba_jobs a;

    SET LINESIZE 80 PAGESIZE 14

Read More

Script: invalid_objects.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Lists all invalid objects in the database.
    -- Call Syntax  : @invalid_objects
    -- Requirements : Access to the DBA views.
       -- -----------------------------------------------------------------------------------
    COLUMN object_name FORMAT A30
    SELECT owner,
           object_type,
           object_name,
           status
    FROM   dba_objects
    WHERE  status = 'INVALID'
    ORDER BY owner, object_type, object_name;

Read More