灵活好用的sql monitoring 脚本 part6

71.Script: jobs.sql

-- Description  : Displays information about all scheduled jobs.

[oracle@MaxwellDBA monitoring]$ cat jobs.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/jobs.sql
-- Author       : Maxwell
-- Description  : Displays information about all scheduled jobs.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @jobs
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$ 

72.Script: jobs_running.sql

-- Description  : Displays information about all jobs currently running.

[oracle@MaxwellDBA monitoring]$ cat jobs_running.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/jobs_running.sql
-- Author       : Maxwell
-- Description  : Displays information about all jobs currently running.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @jobs_running
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$

73.Script: jobs_lob_segments.sql

-- Description  : Displays size of large LOB segments.

[oracle@MaxwellDBA monitoring]$ cat large_lob_segments.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/large_lob_segments.sql
-- Author       : Maxwell
-- Description  : Displays size of large LOB segments.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @large_lob_segments (rows)
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500 VERIFY OFF
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= &1;

SET VERIFY ON
[oracle@MaxwellDBA monitoring]$ 

74.Script: large_segements.sql

-- Description  : Displays size of large segments.

[oracle@MaxwellDBA monitoring]$ cat large_segements.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/large_segments.sql
-- Author       : Maxwell
-- Description  : Displays size of large segments.
-- Requirements : Access to the DBA views.
-- Call Syntax  : @large_segments (rows)
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500 VERIFY OFF
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= &1;

SET VERIFY ON
[oracle@MaxwellDBA monitoring]$

75.Script: latch_hit_ratios.sql

-- Description  : Displays current latch hit ratios.

[oracle@MaxwellDBA monitoring]$ cat latch_hit_ratios.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/latch_hit_ratios.sql
-- Author       : Maxwell
-- Description  : Displays current latch hit ratios.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @latch_hit_ratios
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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;
[oracle@MaxwellDBA monitoring]$

76.Script: latch_holders.sql

-- Description  : Displays information about all current latch holders.

[oracle@MaxwellDBA monitoring]$ cat latch_holder.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/latch_holders.sql
-- Author       : Maxwell
-- Description  : Displays information about all current latch holders.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @latch_holders
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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;
[oracle@MaxwellDBA monitoring]$

77.Script: latches.sql

-- Description  : Displays information about all current latches.

[oracle@MaxwellDBA monitoring]$ cat latches.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/latches.sql
-- Author       : Maxwell
-- Description  : Displays information about all current latches.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @latches
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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;
[oracle@MaxwellDBA monitoring]$

 

78.Script: library_cache.sql

-- Description  : Displays library cache statistics.

[oracle@MaxwellDBA monitoring]$ cat library_cache.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/library_cache.sql
-- Author       : Maxwell
-- Description  : Displays library cache statistics.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @library_cache
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$

79.Script: license.sql

-- Description  : Displays session usage for licensing purposes.

[oracle@MaxwellDBA monitoring]$ cat license.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/license.sql
-- Author       : Maxwell
-- Description  : Displays session usage for licensing purposes.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @license
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
SELECT *
FROM   v$license;
[oracle@MaxwellDBA monitoring]$ 

 

80.Script: locked_objects.sql

-- Description  : Lists all locked objects.

[oracle@MaxwellDBA monitoring]$ cat locked_objects.sql
-- -----------------------------------------------------------------------------------
-- File Name    : /monitoring/locked_objects.sql
-- Author       : Maxwell
-- Description  : Lists all locked objects.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @locked_objects
-- Last Modified: 07-AUG-2022
-- -----------------------------------------------------------------------------------
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
[oracle@MaxwellDBA monitoring]$