• 灵活好用的sql monitoring 脚本 part6

    71.Script: jobs.sql

    -- Description  : Displays information about all scheduled jobs.

    1. [oracle@MaxwellDBA monitoring]$ cat jobs.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/jobs.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all scheduled jobs.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @jobs
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 1000 PAGESIZE 1000
    11.  
    12. COLUMN log_user FORMAT A15
    13. COLUMN priv_user FORMAT A15
    14. COLUMN schema_user FORMAT A15
    15. COLUMN interval FORMAT A40
    16. COLUMN what FORMAT A50
    17. COLUMN nls_env FORMAT A50
    18. COLUMN misc_env FORMAT A50
    19.  
    20. SELECT a.job,            
    21.        a.log_user,       
    22.        a.priv_user,     
    23.        a.schema_user,    
    24.        To_Char(a.last_date,'DD-MON-YYYY HH24:MI:SS') AS last_date,      
    25.        --To_Char(a.this_date,'DD-MON-YYYY HH24:MI:SS') AS this_date,      
    26.        To_Char(a.next_date,'DD-MON-YYYY HH24:MI:SS') AS next_date,      
    27.        a.broken,         
    28.        a.interval,       
    29.        a.failures,       
    30.        a.what,
    31.        a.total_time,     
    32.        a.nls_env,        
    33.        a.misc_env          
    34. FROM   dba_jobs a;
    35.  
    36. SET LINESIZE 80 PAGESIZE 14
    37. [oracle@MaxwellDBA monitoring]$

    72.Script: jobs_running.sql

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

    1. [oracle@MaxwellDBA monitoring]$ cat jobs_running.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/jobs_running.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all jobs currently running.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @jobs_running
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET VERIFY OFF
    13.  
    14. SELECT a.job "Job",
    15.        a.sid,
    16.        a.failures "Failures",       
    17.        Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",      
    18.        Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"             
    19. FROM   dba_jobs_running a;
    20.  
    21. SET PAGESIZE 14
    22. SET VERIFY ON
    23. [oracle@MaxwellDBA monitoring]$

    73.Script: jobs_lob_segments.sql

    -- Description  : Displays size of large LOB segments.

    1. [oracle@MaxwellDBA monitoring]$ cat large_lob_segments.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/large_lob_segments.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays size of large LOB segments.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @large_lob_segments (rows)
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500 VERIFY OFF
    11. COLUMN owner FORMAT A30
    12. COLUMN table_name FORMAT A30
    13. COLUMN column_name FORMAT A30
    14. COLUMN segment_name FORMAT A30
    15. COLUMN tablespace_name FORMAT A30
    16. COLUMN size_mb FORMAT 99999999.00
    17.  
    18. SELECT *
    19. FROM   (SELECT l.owner,
    20.                l.table_name,
    21.                l.column_name,
    22.                l.segment_name,
    23.                l.tablespace_name,
    24.                ROUND(s.bytes/1024/1024,2) size_mb
    25.         FROM   dba_lobs l
    26.                JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
    27.         ORDER BY 6 DESC)
    28. WHERE  ROWNUM <= &1;
    29.  
    30. SET VERIFY ON
    31. [oracle@MaxwellDBA monitoring]$

    74.Script: large_segements.sql

    -- Description  : Displays size of large segments.

    1. [oracle@MaxwellDBA monitoring]$ cat large_segements.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/large_segments.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays size of large segments.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @large_segments (rows)
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500 VERIFY OFF
    11. COLUMN owner FORMAT A30
    12. COLUMN segment_name FORMAT A30
    13. COLUMN tablespace_name FORMAT A30
    14. COLUMN size_mb FORMAT 99999999.00
    15.  
    16. SELECT *
    17. FROM   (SELECT owner,
    18.                segment_name,
    19.                segment_type,
    20.                tablespace_name,
    21.                ROUND(bytes/1024/1024,2) size_mb
    22.         FROM   dba_segments
    23.         ORDER BY 5 DESC)
    24. WHERE  ROWNUM <= &1;
    25.  
    26. SET VERIFY ON
    27. [oracle@MaxwellDBA monitoring]$

    75.Script: latch_hit_ratios.sql

    -- Description  : Displays current latch hit ratios.

    1. [oracle@MaxwellDBA monitoring]$ cat latch_hit_ratios.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/latch_hit_ratios.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays current latch hit ratios.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @latch_hit_ratios
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200
    11.  
    12. COLUMN latch_hit_ratio FORMAT 990.00
    13.  
    14. SELECT l.name,
    15.        l.gets,
    16.        l.misses,
    17.        ((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
    18. FROM   v$latch l
    19. WHERE  l.gets   != 0
    20. UNION
    21. SELECT l.name,
    22.        l.gets,
    23.        l.misses,
    24.        100 AS latch_hit_ratio
    25. FROM   v$latch l
    26. WHERE  l.gets   = 0
    27. ORDER BY 4 DESC;
    28. [oracle@MaxwellDBA monitoring]$

    76.Script: latch_holders.sql

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

    1. [oracle@MaxwellDBA monitoring]$ cat latch_holder.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/latch_holders.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all current latch holders.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @latch_holders
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200
    11.  
    12. SELECT l.name "Latch Name",
    13.        lh.pid "PID",
    14.        lh.sid "SID",
    15.        l.gets "Gets (Wait)",
    16.        l.misses "Misses (Wait)",
    17.        l.sleeps "Sleeps (Wait)",
    18.        l.immediate_gets "Gets (No Wait)",
    19.        l.immediate_misses "Misses (Wait)"
    20. FROM   v$latch l,
    21.        v$latchholder lh
    22. WHERE  l.addr = lh.laddr
    23. ORDER BY l.name;
    24. [oracle@MaxwellDBA monitoring]$

    77.Script: latches.sql

    -- Description  : Displays information about all current latches.

    1. [oracle@MaxwellDBA monitoring]$ cat latches.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/latches.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all current latches.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @latches
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200
    11.  
    12. SELECT l.latch#,
    13.        l.name,
    14.        l.gets,
    15.        l.misses,
    16.        l.sleeps,
    17.        l.immediate_gets,
    18.        l.immediate_misses,
    19.        l.spin_gets
    20. FROM   v$latch l
    21. ORDER BY l.name;
    22. [oracle@MaxwellDBA monitoring]$

     

    78.Script: library_cache.sql

    -- Description  : Displays library cache statistics.

    1. [oracle@MaxwellDBA monitoring]$ cat library_cache.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/library_cache.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays library cache statistics.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @library_cache
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET VERIFY OFF
    13.  
    14. SELECT a.namespace "Name Space",
    15.        a.gets "Get Requests",
    16.        a.gethits "Get Hits",
    17.        Round(a.gethitratio,2) "Get Ratio",
    18.        a.pins "Pin Requests",
    19.        a.pinhits "Pin Hits",
    20.        Round(a.pinhitratio,2) "Pin Ratio",
    21.        a.reloads "Reloads",
    22.        a.invalidations "Invalidations"
    23. FROM   v$librarycache a
    24. ORDER BY 1;
    25.  
    26. SET PAGESIZE 14
    27. SET VERIFY ON
    28. [oracle@MaxwellDBA monitoring]$

    79.Script: license.sql

    -- Description  : Displays session usage for licensing purposes.

    1. [oracle@MaxwellDBA monitoring]$ cat license.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/license.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays session usage for licensing purposes.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @license
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SELECT *
    11. FROM   v$license;
    12. [oracle@MaxwellDBA monitoring]$

     

    80.Script: locked_objects.sql

    -- Description  : Lists all locked objects.

    1. [oracle@MaxwellDBA monitoring]$ cat locked_objects.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/locked_objects.sql
    4. -- Author       : Maxwell
    5. -- Description  : Lists all locked objects.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @locked_objects
    8. -- Last Modified: 07-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET VERIFY OFF
    13.  
    14. COLUMN owner FORMAT A20
    15. COLUMN username FORMAT A20
    16. COLUMN object_owner FORMAT A20
    17. COLUMN object_name FORMAT A30
    18. COLUMN locked_mode FORMAT A15
    19.  
    20. SELECT lo.session_id AS sid,
    21.        s.serial#,
    22.        NVL(lo.oracle_username, '(oracle)') AS username,
    23.        o.owner AS object_owner,
    24.        o.object_name,
    25.        Decode(lo.locked_mode, 0, 'None',
    26.                               1, 'Null (NULL)',
    27.                               2, 'Row-S (SS)',
    28.                               3, 'Row-X (SX)',
    29.                               4, 'Share (S)',
    30.                               5, 'S/Row-X (SSX)',
    31.                               6, 'Exclusive (X)',                                                                                                                                    lo.locked_mode) locked_mode,
    32.        lo.os_user_name
    33. FROM   v$locked_object lo
    34.        JOIN dba_objects o ON o.object_id = lo.object_id
    35.        JOIN v$session s ON lo.session_id = s.sid
    36. ORDER BY 1, 2, 3, 4;
    37.  
    38. SET PAGESIZE 14
    39. SET VERIFY ON
    40. [oracle@MaxwellDBA monitoring]$

  • 相关阅读:
    Android 开发学习(二)
    CTF/AWD竞赛标准参考书+实战指南：《AWD特训营》
    OC高仿iOS网易云音乐AFNetworking+SDWebImage+MJRefresh+MVC+MVVM
    当我们做后仿时我们究竟在仿些什么（二）
    文章解读与仿真程序复现思路——电网技术EI\CSCD\北大核心《基于相似日聚类及模态分解的短期光伏发电功率组合预测研究》
    Docker：Elasticsearch安装配置IK分词器
    TCP通信实现：多发多收消息、实现可以同时接收多个客户端
    基于Matlab的超像素图像分割
    Linux的Jdk安装教程
    Windows与网络基础-26-IP地址概述
  • 原文地址：https://blog.csdn.net/u011868279/article/details/126199221