Script: controlfiles.sql

    -- -----------------------------------------------------------------------------------
-- Description  : Displays information about controlfiles.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @controlfiles
-- -----------------------------------------------------------------------------------

SET LINESIZE 100
COLUMN name FORMAT A80

SELECT name,
       status
FROM   v$controlfile
ORDER BY name;

SET LINESIZE 80

Read More

Script: max_extents.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays all tables and indexes nearing their MAX_EXTENTS setting.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @max_extents
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    PROMPT
    PROMPT Tables and Indexes nearing MAX_EXTENTS
    PROMPT **************************************
    SELECT e.owner,
           e.segment_type,
           Substr(e.segment_name, 1, 30) segment_name,
           Trunc(s.initial_extent/1024) "INITIAL K",
           Trunc(s.next_extent/1024) "NEXT K",
           s.max_extents,
           Count(*) as extents
    FROM   dba_extents e,
           dba_segments s
    WHERE  e.owner        = s.owner
    AND    e.segment_name = s.segment_name
    AND    e.owner        NOT IN ('SYS', 'SYSTEM')
    GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
    HAVING Count(*) > s.max_extents - 10
    ORDER BY e.owner, e.segment_type, Count(*) DESC;

Read More

Script: lru_latch_ratio.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays current LRU latch ratios.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @lru_latch_hit_ratio
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    COLUMN "Ratio %" FORMAT 990.00
    
    PROMPT
    PROMPT Values greater than 3% indicate contention.

    SELECT a.child#,
           (a.SLEEPS / a.GETS) * 100 "Ratio %"
    FROM   v$latch_children a
    WHERE  a.name      = 'cache buffers lru chain'
    ORDER BY 1;


    SET PAGESIZE 14

Read More

Script: longops.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all long operations.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @longops
        -- -----------------------------------------------------------------------------------

    COLUMN sid FORMAT 999
    COLUMN serial# FORMAT 9999999
    COLUMN machine FORMAT A30
    COLUMN progress_pct FORMAT 99999999.00
    COLUMN elapsed FORMAT A10
    COLUMN remaining FORMAT A10

    SELECT s.sid,
           s.serial#,
           s.machine,
           ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
           ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
           ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
    FROM   v$session s,
           v$session_longops sl
    WHERE  s.sid     = sl.sid
    AND    s.serial# = sl.serial#;

Read More

Script: logfiles.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about redo log files.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @logfiles
        -- -----------------------------------------------------------------------------------

    SET LINESIZE 200
    COLUMN member FORMAT A50
    COLUMN first_change# FORMAT 99999999999999999999
    COLUMN next_change# FORMAT 99999999999999999999

    SELECT l.thread#,
           lf.group#,
           lf.member,
           TRUNC(l.bytes/1024/1024) AS size_mb,
           l.status,
           l.archived,
           lf.type,
           lf.is_recovery_dest_file AS rdf,
           l.sequence#,
           l.first_change#,
           l.next_change#  
    FROM   v$logfile lf
           JOIN v$log l ON l.group# = lf.group#
    ORDER BY l.thread#,lf.group#, lf.member;

    SET LINESIZE 80

Read More

Script: locked_objects_internal.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Lists all locks on the specific object.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @locked_objects_internal (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 1000 VERIFY OFF

    COLUMN lock_type FORMAT A20
    COLUMN mode_held FORMAT A10
    COLUMN mode_requested FORMAT A10
    COLUMN lock_id1 FORMAT A50
    COLUMN lock_id2 FORMAT A30

    SELECT li.session_id AS sid,
           s.serial#,
           li.lock_type,
           li.mode_held,
           li.mode_requested,
           li.lock_id1,
           li.lock_id2
    FROM   dba_lock_internal li
           JOIN v$session s ON li.session_id = s.sid
    WHERE  UPPER(lock_id1) LIKE '%&1%';

    SET VERIFY ON

Read More

Script: locked_objects.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Lists all locked objects.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @locked_objects
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    COLUMN owner FORMAT A20
    COLUMN username FORMAT A20
    COLUMN object_owner FORMAT A20
    COLUMN object_name FORMAT A30
    COLUMN locked_mode FORMAT A15

    SELECT lo.session_id AS sid,
           s.serial#,
           NVL(lo.oracle_username, '(oracle)') AS username,
           o.owner AS object_owner,
           o.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)',
                                 lo.locked_mode) locked_mode,
           lo.os_user_name
    FROM   v$locked_object lo
           JOIN dba_objects o ON o.object_id = lo.object_id
           JOIN v$session s ON lo.session_id = s.sid
    ORDER BY 1, 2, 3, 4;

    SET PAGESIZE 14
    SET VERIFY ON

Read More