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

Script: license.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays session usage for licensing purposes.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @license
    -- 
    -- -----------------------------------------------------------------------------------
    SELECT *
    FROM   v$license;

Read More

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

Script: index_usage.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Shows the usage for the specified table indexes.
    -- Call Syntax  : @index_usage (table-name) (index-name or all)
        -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 200

    SELECT table_name,
           index_name,
           used,
           start_monitoring,
           end_monitoring
    FROM   v$object_usage
    WHERE  table_name = UPPER('&1')
    AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

Read More

Script: index_partitions.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays partition information for the specified index, or all indexes.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @index_patitions (index_name or all) (schema-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET FEEDBACK OFF
    SET VERIFY OFF

    SELECT a.index_name,
           a.partition_name,
           a.tablespace_name,
           a.initial_extent,
           a.next_extent,
           a.pct_increase,
           a.num_rows
    FROM   dba_ind_partitions a
    WHERE  a.index_name  = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
    AND    a.index_owner = Upper('&&2')
    ORDER BY a.index_name, a.partition_name
    /

    PROMPT
    SET PAGESIZE 14
    SET FEEDBACK ON

Read More

Script: index_monitoring_status.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Shows the monitoring status for the specified table indexes.
    -- Call Syntax  : @index_monitoring_status (schema) (table-name or all)
        -- -----------------------------------------------------------------------------------
    SET VERIFY OFF

    SELECT table_name,
           index_name,
           monitoring
    FROM   v$object_usage
    WHERE  table_name = UPPER('&1')
    AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

Read More

Script: index_extents.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays number of extents for all indexes belonging to the specified table, or all tables.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @index_extents (table_name or all) (schema-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT i.index_name,
           Count(e.segment_name) extents,
           i.max_extents,
           t.num_rows "ROWS",
           Trunc(i.initial_extent/1024) "INITIAL K",
           Trunc(i.next_extent/1024) "NEXT K",
           t.table_name
    FROM   all_tables t,
           all_indexes i,
           dba_extents e
    WHERE  i.table_name   = t.table_name
    AND    i.owner        = t.owner
    AND    e.segment_name = i.index_name
    AND    e.owner        = i.owner
    AND    i.table_name   = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
    AND    i.owner        = Upper('&&2')
    GROUP BY t.table_name,
             i.index_name,
             i.max_extents,
             t.num_rows,
             i.initial_extent,
             i.next_extent
    HAVING   Count(e.segment_name) > 5
    ORDER BY Count(e.segment_name) DESC;

    SET PAGESIZE 18
    SET VERIFY ON

Read More

Script: identify_trace_file.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the name of the trace file associated with the current session.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @identify_trace_file
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 100
    COLUMN trace_file FORMAT A60

    SELECT s.sid,
           s.serial#,
           pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   
           '_ora_' || p.spid || '.trc' AS trace_file
    FROM   v$session s,
           v$process p,
           v$parameter pa
    WHERE  pa.name = 'user_dump_dest'
    AND    s.paddr = p.addr
    AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Read More

Script: hot_blocks.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Detects hot blocks.
    -- Call Syntax  : @hot_blocks
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 200
    SET VERIFY OFF

    SELECT *
    FROM   (SELECT name,
                   addr,
                   gets,
                   misses,
                   sleeps
            FROM   v$latch_children
            WHERE  name = 'cache buffers chains'
            AND    misses > 0
            ORDER BY misses DESC)
    WHERE  rownum < 11;

    ACCEPT address PROMPT "Enter ADDR: "

    COLUMN owner FORMAT A15
    COLUMN object_name FORMAT A30
    COLUMN subobject_name FORMAT A20

    SELECT *
    FROM   (SELECT o.owner,
                   o.object_name,
                   o.subobject_name,
                   bh.tch,
                   bh.obj,
                   bh.file#,
                   bh.dbablk,
                   bh.class,
                   bh.state
            FROM   x$bh bh,
                   dba_objects o
            WHERE  o.data_object_id = bh.obj
            AND    hladdr = '&address'
            ORDER BY tch DESC)
    WHERE  rownum < 11;

Read More

Script: hidden_parameters.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays a list of one or all the hidden parameters.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @hidden_parameters (parameter-name or all)
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    COLUMN parameter      FORMAT a37
    COLUMN description    FORMAT a30 WORD_WRAPPED
    COLUMN session_value  FORMAT a10
    COLUMN instance_value FORMAT a10
    
    SELECT a.ksppinm AS parameter,
           a.ksppdesc AS description,
           b.ksppstvl AS session_value,
           c.ksppstvl AS instance_value
    FROM   x$ksppi a,
           x$ksppcv b,
           x$ksppsv c
    WHERE  a.indx = b.indx
    AND    a.indx = c.indx
    AND    a.ksppinm LIKE '/_%' ESCAPE '/'
    AND    a.ksppinm = DECODE(LOWER('&1'), 'all', a.ksppinm, LOWER('&1'))
    ORDER BY a.ksppinm;

Read More

Script: high_water_mark.sql

    -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the High Water Mark for the specified table, or all tables.
    -- Requirements : Access to the Dbms_Space.
    -- Call Syntax  : @high_water_mark (table_name or all) (schema-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET SERVEROUTPUT ON
    SET VERIFY OFF

    DECLARE
      CURSOR cu_tables IS
        SELECT a.owner,
               a.table_name
        FROM   all_tables a
        WHERE  a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
        AND    a.owner      = Upper('&&2');

      op1  NUMBER;
      op2  NUMBER;
      op3  NUMBER;
      op4  NUMBER;
      op5  NUMBER;
      op6  NUMBER;
      op7  NUMBER;
    BEGIN

      Dbms_Output.Disable;
      Dbms_Output.Enable(1000000);
      Dbms_Output.Put_Line('TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK');
      Dbms_Output.Put_Line('------------------------------  ---------------  ---------------  ---------------');
      FOR cur_rec IN cu_tables LOOP
        Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
        Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                             LPad(op3,15,' ')                ||
                             LPad(op1,15,' ')                ||
                             LPad(Trunc(op1-op3-1),15,' '));
      END LOOP;

    END;
    /

    SET VERIFY ON

Read More

Script: fks.sql

-- -----------------------------------------------------------------------------------
      -- Author       : Pavan Yennampelli
    -- Description  : Displays the constraints on a specific table and those referencing it.
    -- Call Syntax  : @fks (table-name) (schema)
    -- 
    -- -----------------------------------------------------------------------------------
    PROMPT
    SET VERIFY OFF
    SET FEEDBACK OFF
    SET LINESIZE 255
    SET PAGESIZE 1000

    PROMPT
    PROMPT Constraints Owned By Table
    PROMPT ==========================
    SELECT c.constraint_name "Constraint",
           Decode(c.constraint_type,'P','Primary Key',
                                    'U','Unique Key',
                                    'C','Check',
                                    'R','Foreign Key',
                                    c.constraint_type) "Type",
           c.r_owner "Ref Table",
           c.r_constraint_name "Ref Constraint"
    FROM   all_constraints c
    WHERE  c.table_name = Upper('&&1')
    AND    c.owner      = Upper('&&2');


    PROMPT
    PROMPT Constraints Referencing Table
    PROMPT =============================
    SELECT c1.table_name "Table",
           c1.constraint_name "Foreign Key",
           c1.r_constraint_name "References"
    FROM   all_constraints c1
    WHERE  c1.owner      = Upper('&&2')
    AND    c1.r_constraint_name IN (SELECT c2.constraint_name
                                    FROM   all_constraints c2
                                    WHERE  c2.table_name = Upper('&&1')
                                    AND    c2.owner      = Upper('&&2')
                                    AND    c2.constraint_type IN ('P','U'));

    SET VERIFY ON
    SET FEEDBACK ON
    SET PAGESIZE 1000
    PROMPT

Read More

Script: health.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Lots of information about the database so you can asses the general health of the system.
    -- Requirements : Access to the V$ & DBA views and several other monitoring scripts.
    -- Call Syntax  : @health (username/password@service)
    -- 
    -- -----------------------------------------------------------------------------------
    SPOOL Health_Checks.txt
    conn &1
    @db_info
    @sessions
    @ts_full
    @max_extents

    SPOOL OFF

Read More

Script: free_space.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays space usage for each datafile.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @free_space
        -- -----------------------------------------------------------------------------------
    SET PAGESIZE 100
    SET LINESIZE 265

    COLUMN tablespace_name FORMAT A20
    COLUMN file_name FORMAT A50

    SELECT df.tablespace_name,
           df.file_name,
           df.size_mb,
           f.free_mb,
           df.max_size_mb,
           f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb,
           RPAD(' '|| RPAD('X',ROUND((df.max_size_mb-(f.free_mb + (df.max_size_mb - df.size_mb)))/max_size_mb*10,0), 'X'),11,'-') AS used_pct
    FROM   (SELECT file_id,
                   file_name,
                   tablespace_name,
                   TRUNC(bytes/1024/1024) AS size_mb,
                   TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
            FROM   dba_data_files) df,
           (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
                   file_id
            FROM dba_free_space
            GROUP BY file_id) f
    WHERE  df.file_id = f.file_id (+)
    ORDER BY df.tablespace_name,
             df.file_name;

    PROMPT
    SET PAGESIZE 14

Read More

Script: code_dep_distinct.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays a tree of dependencies of specified object.
    -- Call Syntax  : @code_dep_distinct (schema-name) (object-name) (object_type or all)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000
    COLUMN referenced_object FORMAT A50
    COLUMN referenced_type FORMAT A20
    COLUMN referenced_link_name FORMAT A20

    SELECT DISTINCT a.referenced_owner || '.' || a.referenced_name AS referenced_object,
           a.referenced_type,
           a.referenced_link_name
    FROM   all_dependencies a
    WHERE  a.owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_type != 'NON-EXISTENT'
    AND    a.referenced_type = DECODE(UPPER('&3'), 'ALL', a.referenced_type, UPPER('&3'))
    START WITH a.owner = UPPER('&1')
    AND        a.name  = UPPER('&2')
    CONNECT BY a.owner = PRIOR a.referenced_owner
    AND        a.name  = PRIOR a.referenced_name
    AND        a.type  = PRIOR a.referenced_type;

    SET VERIFY ON
    SET PAGESIZE 22

Read More

Script: dispatchers.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays dispatcher statistics.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @dispatchers
      -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT a.name "Name",
           a.status "Status",
           a.accept "Accept",
           a.messages "Total Mesgs",
           a.bytes "Total Bytes",
           a.owned "Circs Owned",
           a.idle "Total Idle Time",
           a.busy "Total Busy Time",
           Round(a.busy/(a.busy + a.idle),2) "Load"
    FROM   v$dispatcher a
    ORDER BY 1;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: fk_columns.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all FKs for the specified schema and table.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @fk_columns (schema-name or all) (table-name or all)
     -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 1000
    COLUMN column_name FORMAT A30
    COLUMN r_column_name FORMAT A30

    SELECT c.constraint_name,
           cc.table_name,
           cc.column_name,
           rcc.table_name AS r_table_name,
           rcc.column_name AS r_column_name,
           cc.position
    FROM   dba_constraints c
           JOIN dba_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
           JOIN dba_cons_columns rcc ON c.owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name AND cc.position = rcc.position
    WHERE  c.owner      = DECODE(UPPER('&1'), 'ALL', c.owner, UPPER('&1'))
    AND    c.table_name = DECODE(UPPER('&2'), 'ALL', c.table_name, UPPER('&2'))
    ORDER BY c.constraint_name, cc.table_name, cc.position;

Read More

Script: file_io.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the amount of IO for each datafile.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @file_io
    -- Last Modified: 15-JUL-2000
    -- -----------------------------------------------------------------------------------
    SET PAGESIZE 1000

    SELECT Substr(d.name,1,50) "File Name",
           f.phyblkrd "Blocks Read",
           f.phyblkwrt "Blocks Writen",
           f.phyblkrd + f.phyblkwrt "Total I/O"
    FROM   v$filestat f,
           v$datafile d
    WHERE  d.file# = f.file#
    ORDER BY f.phyblkrd + f.phyblkwrt DESC;

    SET PAGESIZE 18

Read More

Script: explain.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays a tree-style execution plan of the specified statement after it has been explained.
    -- Requirements : Access to the plan table.
    -- Call Syntax  : @explain (statement-id)
    -- -----------------------------------------------------------------------------------
    SET PAGESIZE 100
    SET LINESIZE 200
    SET VERIFY OFF

    COLUMN plan             FORMAT A50
    COLUMN object_name      FORMAT A30
    COLUMN object_type      FORMAT A15
    COLUMN bytes            FORMAT 9999999999
    COLUMN cost             FORMAT 9999999
    COLUMN partition_start  FORMAT A20
    COLUMN partition_stop   FORMAT A20

    SELECT LPAD(' ', 2 * (level - 1)) ||
           DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
           INITCAP(pt.operation) ||
           DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
           pt.object_name,
           pt.object_type,
           pt.bytes,
           pt.cost,
           pt.partition_start,
           pt.partition_stop
    FROM   plan_table pt
    START WITH pt.id = 0
      AND pt.statement_id = '&1'
    CONNECT BY PRIOR pt.id = pt.parent_id
      AND pt.statement_id = '&1';

Read More

Script: error_stack.sql

    -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays contents of the error stack.
    -- Call Syntax  : @error_stack
    -- 
    -- -----------------------------------------------------------------------------------
    SET SERVEROUTPUT ON
    DECLARE
      v_stack  VARCHAR2(2000);
    BEGIN
      v_stack := Dbms_Utility.Format_Error_Stack;
      Dbms_Output.Put_Line(v_stack);
    END;
    /

Read More

Script: directories.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all directories.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @directories
     -- -----------------------------------------------------------------------------------
    COLUMN owner FORMAT A20
    COLUMN directory_name FORMAT A25
    COLUMN directory_path FORMAT A50

    SELECT *
    FROM   dba_directories
    ORDER BY owner, directory_name;

Read More

Script: df_free_space.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays free space information about datafiles.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @df_free_space.sql
    -- -----------------------------------------------------------------------------------

    SET LINESIZE 120
    COLUMN file_name FORMAT A60

    SELECT a.file_name,
           ROUND(a.bytes/1024/1024) AS size_mb,
           ROUND(a.maxbytes/1024/1024) AS maxsize_mb,
           ROUND(b.free_bytes/1024/1024) AS free_mb,
           ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb,
           100 - ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used
    FROM   (SELECT file_name,
                   file_id,
                   bytes,
                   GREATEST(bytes,maxbytes) AS maxbytes,
                   GREATEST(bytes,maxbytes)-bytes AS growth
            FROM   dba_data_files) a,
           (SELeCT file_id,
                   SUM(bytes) AS free_bytes
            FROM   dba_free_space
            GROUP BY file_id) b
    WHERE  a.file_id = b.file_id
    ORDER BY file_name;

Read More

Script: db_properties.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays all database property values.
    -- Call Syntax  : @db_properties
    -- -----------------------------------------------------------------------------------
    COLUMN property_value FORMAT A50

    SELECT property_name,
           property_value
    FROM   database_properties
    ORDER BY property_name;

Read More

Script: db_info.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays general information about the database.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @db_info
    -- 
    -- -----------------------------------------------------------------------------------
    SET PAGESIZE 1000
    SET LINESIZE 100
    SET FEEDBACK OFF

    SELECT *
    FROM   v$database;

    SELECT *
    FROM   v$instance;

    SELECT *
    FROM   v$version;

    SELECT a.name,
           a.value
    FROM   v$sga a;

    SELECT Substr(c.name,1,60) "Controlfile",
           NVL(c.status,'UNKNOWN') "Status"
    FROM   v$controlfile c
    ORDER BY 1;

    SELECT Substr(d.name,1,60) "Datafile",
           NVL(d.status,'UNKNOWN') "Status",
           d.enabled "Enabled",
           LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
    FROM   v$datafile d
    ORDER BY 1;

    SELECT l.group# "Group",
           Substr(l.member,1,60) "Logfile",
           NVL(l.status,'UNKNOWN') "Status"
    FROM   v$logfile l
    ORDER BY 1,2;

    PROMPT
    SET PAGESIZE 14
    SET FEEDBACK ON

Read More

Script: db_links_open.sql

-- -----------------------------------------------------------------------------------
     -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all open database links.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @db_links_open
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    COLUMN db_link FORMAT A30

    SELECT db_link,
           owner_id,
           logged_on,
           heterogeneous,
           protocol,
           open_cursors,
           in_transaction,
           update_sent,
           commit_point_strength
    FROM   v$dblink
    ORDER BY db_link;

    SET LINESIZE 80

Read More

Script: db_links.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all database links.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @db_links
       -- -----------------------------------------------------------------------------------
    SET LINESIZE 150

    COLUMN db_link FORMAT A30
    COLUMN host FORMAT A30

    SELECT owner,
           db_link,
           username,
           host
    FROM   dba_db_links
    ORDER BY owner, db_link;

Read More

Script: db_cache_advice.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Predicts how changes to the buffer cache will affect physical reads.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @db_cache_advice
       -- -----------------------------------------------------------------------------------

    COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'
    COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'
    COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'
    COLUMN estd_physical_reads        FORMAT 999,999,999,999 heading 'Estd Phys| Reads'

    SELECT size_for_estimate,
           buffers_for_estimate,
           estd_physical_read_factor,
           estd_physical_reads
    FROM   v$db_cache_advice
    WHERE  name          = 'DEFAULT'
    AND    block_size    = (SELECT value
                            FROM   v$parameter
                            WHERE  name = 'db_block_size')
    AND    advice_status = 'ON';

Read More

Script: datafiles.sql

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

    SET LINESIZE 200
    COLUMN file_name FORMAT A70

    SELECT file_id,
           file_name,
           ROUND(bytes/1024/1024/1024) AS size_gb,
           ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
           autoextensible,
           increment_by,
           status
    FROM   dba_data_files
    ORDER BY file_name;

Read More

Script: column_defaults.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the default values where present for the specified table.
    -- Call Syntax  : @column_defaults (table-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 100
    SET VERIFY OFF

    SELECT a.column_name "Column",
           a.data_default "Default"
    FROM   all_tab_columns a
    WHERE  a.table_name = Upper('&1')
    AND    a.data_default IS NOT NULL
    /

Read More

Script: code_dep_tree.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays a tree of dependencies of specified object.
    -- Call Syntax  : @code_dep_tree (schema-name) (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000

    COLUMN referenced_object FORMAT A50
    COLUMN referenced_type FORMAT A20
    COLUMN referenced_link_name FORMAT A20

    SELECT RPAD(' ', level*2, ' ') || a.referenced_owner || '.' || a.referenced_name AS referenced_object,
           a.referenced_type,
           a.referenced_link_name
    FROM   all_dependencies a
    WHERE  a.owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_type != 'NON-EXISTENT'
    START WITH a.owner = UPPER('&1')
    AND        a.name  = UPPER('&2')
    CONNECT BY a.owner = PRIOR a.referenced_owner
    AND        a.name  = PRIOR a.referenced_name
    AND        a.type  = PRIOR a.referenced_type;

    SET VERIFY ON
    SET PAGESIZE 22

Read More

Script: code_dep_on.sql

-- -----------------------------------------------------------------------------------
     -- Author       : Pavan Yennampelli
    -- Description  : Displays all objects dependant on the specified object.
    -- Call Syntax  : @code_dep_on (schema-name or all) (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000
    BREAK ON type SKIP 1
    COLUMN owner FORMAT A20
    SELECT a.type,
           a.owner,
           a.name
    FROM   all_dependencies a
    WHERE  a.referenced_owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
    AND    a.referenced_name  = UPPER('&2')
    ORDER BY 1,2,3;
    SET PAGESIZE 22
    SET VERIFY ON

Read More

Script: code_dep.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays all dependencies of specified object.
    -- Call Syntax  : @code_dep (schema-name or all) (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000
    BREAK ON referenced_type SKIP 1
    COLUMN referenced_type FORMAT A20
    COLUMN referenced_owner FORMAT A20
    COLUMN referenced_name FORMAT A40
    COLUMN referenced_link_name FORMAT A20
    SELECT a.referenced_type,
           a.referenced_owner,
           a.referenced_name,
           a.referenced_link_name
    FROM   all_dependencies a
    WHERE  a.owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
    AND    a.name  = UPPER('&2')
    ORDER BY 1,2,3;
    SET VERIFY ON
    SET PAGESIZE 22

Read More

Script: call_stack.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the current call stack.
    -- Requirements : Access to DBMS_UTILITY.
    -- Call Syntax  : @call_stack
    -- 
    -- -----------------------------------------------------------------------------------
    SET SERVEROUTPUT ON
    DECLARE
      v_stack  VARCHAR2(2000);
    BEGIN
      v_stack := Dbms_Utility.Format_Call_Stack;
      Dbms_Output.Put_Line(v_stack);
    END;
    /

Read More

Script: cache_hit_ratio.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays cache hit ratio for the database.
    -- Comments     : The minimum figure of 89% is often quoted, but depending on the type of system this may not be possible.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @cache_hit_ratio
       -- -----------------------------------------------------------------------------------
    PROMPT
    PROMPT Hit ratio should exceed 89%
    SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
           Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
           Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
           Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0)) -
             Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
               (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
                 Sum(Decode(a.name, 'db block gets', a.value, 0))))
                 *100,2) "Hit Ratio %"
    FROM   v$sysstat a;

Read More

Script: active_sessions.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all active database sessions.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @active_sessions
        -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000

    COLUMN username FORMAT A15
    COLUMN machine FORMAT A25
    COLUMN logon_time FORMAT A20

    SELECT NVL(s.username, '(oracle)') AS username,
           s.osuser,
           s.sid,
           s.serial#,
           p.spid,
           s.lockwait,
           s.status,
           s.module,
           s.machine,
           s.program,
           TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
    FROM   v$session s,
           v$process p
    WHERE  s.paddr  = p.addr
    AND    s.status = 'ACTIVE'
    ORDER BY s.username, s.osuser;

    SET PAGESIZE 14

Read More