• 灵活好用的sql monitoring 脚本 part3

    9.Script: code_dep.sql

    -- Description  : Displays all dependencies of specified object.

    1. [oracle@MaxwellDBA monitoring]$ cat code_dep.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/code_dep.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays all dependencies of specified object.
    6. -- Call Syntax  : @code_dep (schema-name or all) (object-name)
    7. -- Last Modified: 05-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET VERIFY OFF
    10. SET LINESIZE 255
    11. SET PAGESIZE 1000
    12. BREAK ON referenced_type SKIP 1
    13.  
    14. COLUMN referenced_type FORMAT A20
    15. COLUMN referenced_owner FORMAT A20
    16. COLUMN referenced_name FORMAT A40
    17. COLUMN referenced_link_name FORMAT A20
    18.  
    19. SELECT a.referenced_type,
    20.        a.referenced_owner,
    21.        a.referenced_name,
    22.        a.referenced_link_name
    23. FROM   all_dependencies a
    24. WHERE  a.owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
    25. AND    a.name  = UPPER('&2')
    26. ORDER BY 1,2,3;
    27.  
    28. SET VERIFY ON
    29. SET PAGESIZE 22
    30. [oracle@MaxwellDBA monitoring]$ 
    1. SQL> @/home/oracle/oracledba/monitoring/code_dep.sql SYS USER$
    2.  
    3. no rows selected
    4.  
    5. SQL> @/home/oracle/oracledba/monitoring/code_dep.sql ALL USER$
    6.  
    7. no rows selected
    8.  
    9. SQL> @/home/oracle/oracledba/monitoring/code_dep.sql ALL ALL
    10.  
    11. no rows selected
    12.  
    13. SQL>

    10.Script: code_dep_distinct.sql

    -- Description  : Displays a tree of dependencies of specified object.

    1. [oracle@MaxwellDBA monitoring]$ cat code_dep_distinct.sql
    2.  
    3. -- -----------------------------------------------------------------------------------
    4. -- File Name    : /monitoring/code_dep_distinct.sql
    5. -- Author       : Maxwell
    6. -- Description  : Displays a tree of dependencies of specified object.
    7. -- Call Syntax  : @code_dep_distinct (schema-name) (object-name) (object_type or all)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET VERIFY OFF
    11. SET LINESIZE 255
    12. SET PAGESIZE 1000
    13.  
    14. COLUMN referenced_object FORMAT A50
    15. COLUMN referenced_type FORMAT A20
    16. COLUMN referenced_link_name FORMAT A20
    17.  
    18. SELECT DISTINCT a.referenced_owner || '.' || a.referenced_name AS referenced_object,
    19.        a.referenced_type,
    20.        a.referenced_link_name
    21. FROM   all_dependencies a
    22. WHERE  a.owner NOT IN ('SYS','SYSTEM','PUBLIC')
    23. AND    a.referenced_owner NOT IN ('SYS','SYSTEM','PUBLIC')
    24. AND    a.referenced_type != 'NON-EXISTENT'
    25. AND    a.referenced_type = DECODE(UPPER('&3'), 'ALL', a.referenced_type, UPPER('&3'))
    26. START WITH a.owner = UPPER('&1')
    27. AND        a.name  = UPPER('&2')
    28. CONNECT BY a.owner = PRIOR a.referenced_owner
    29. AND        a.name  = PRIOR a.referenced_name
    30. AND        a.type  = PRIOR a.referenced_type;
    31.  
    32. SET VERIFY ON
    33. SET PAGESIZE 22
    34. [oracle@MaxwellDBA monitoring]$

    11.Script: code_dep_on.sql

    -- Description  : Displays all objects dependant on the specified object.

    1. [oracle@MaxwellDBA monitoring]$ cat code_dep_on.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/code_dep_on.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays all objects dependant on the specified object.
    6. -- Call Syntax  : @code_dep_on (schema-name or all) (object-name)
    7. -- Last Modified: 05-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET VERIFY OFF
    10. SET LINESIZE 255
    11. SET PAGESIZE 1000
    12. BREAK ON type SKIP 1
    13.  
    14. COLUMN owner FORMAT A20
    15.  
    16. SELECT a.type,
    17.        a.owner,
    18.        a.name
    19. FROM   all_dependencies a
    20. WHERE  a.referenced_owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
    21. AND    a.referenced_name  = UPPER('&2')
    22. ORDER BY 1,2,3;
    23.  
    24. SET PAGESIZE 22
    25. SET VERIFY ON
    26. [oracle@MaxwellDBA monitoring]$

    12.Script: code_dep_tree.sql

    -- Description  : Displays a tree of dependencies of specified object.

    1. [oracle@MaxwellDBA monitoring]$ cat code_dep_tree.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/code_dep_tree.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a tree of dependencies of specified object.
    6. -- Call Syntax  : @code_dep_tree (schema-name) (object-name)
    7. -- Last Modified: 05-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET VERIFY OFF
    10. SET LINESIZE 255
    11. SET PAGESIZE 1000
    12.  
    13. COLUMN referenced_object FORMAT A50
    14. COLUMN referenced_type FORMAT A20
    15. COLUMN referenced_link_name FORMAT A20
    16.  
    17. SELECT RPAD(' ', level*2, ' ') || a.referenced_owner || '.' || a.referenced_name AS referenced_object,
    18.        a.referenced_type,
    19.        a.referenced_link_name
    20. FROM   all_dependencies a
    21. WHERE  a.owner NOT IN ('SYS','SYSTEM','PUBLIC')
    22. AND    a.referenced_owner NOT IN ('SYS','SYSTEM','PUBLIC')
    23. AND    a.referenced_type != 'NON-EXISTENT'
    24. START WITH a.owner = UPPER('&1')
    25. AND        a.name  = UPPER('&2')
    26. CONNECT BY a.owner = PRIOR a.referenced_owner
    27. AND        a.name  = PRIOR a.referenced_name
    28. AND        a.type  = PRIOR a.referenced_type;
    29.  
    30. SET VERIFY ON
    31. SET PAGESIZE 22
    32. [oracle@MaxwellDBA monitoring]$

    13.obj_lock.sql

    -- Description  : Displays a list of locked objects.

    1. [oracle@MaxwellDBA monitoring]$ cat obj_lock.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/obj_lock.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of locked objects.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @obj_lock
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SELECT a.type,
    11.        Substr(a.owner,1,30) owner,
    12.        a.sid,
    13.        Substr(a.object,1,30) object
    14. FROM   v$access a
    15. WHERE  a.owner NOT IN ('SYS','PUBLIC')
    16. ORDER BY 1,2,3,4
    17. /
    18. [oracle@MaxwellDBA monitoring]$

    14.object_privs.sql

    -- Description  : Displays object privileges on a specified object.

    1. [oracle@MaxwellDBA monitoring]$ cat object_privs.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/object_privs.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays object privileges on a specified object.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @object_privs (owner) (object-name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200 VERIFY OFF
    11.  
    12. COLUMN owner FORMAT A30
    13. COLUMN object_name FORMAT A30
    14. COLUMN grantor FORMAT A30
    15. COLUMN grantee FORMAT A30
    16.  
    17. SELECT owner,
    18.        table_name AS object_name,
    19.        grantor,
    20.        grantee,
    21.        privilege,
    22.        grantable,
    23.        hierarchy
    24. FROM   dba_tab_privs
    25. WHERE  owner      = UPPER('&1')
    26. AND    table_name = UPPER('&2')
    27. ORDER BY 1,2,3,4;
    28.  
    29. SET VERIFY ON
    30. [oracle@MaxwellDBA monitoring]$

    15.object_status.sql

    -- Description  : Displays a list of objects and their status for the specific schema.

    1. [oracle@MaxwellDBA monitoring]$ cat object_status.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/object_status.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of objects and their status for the specific schema.
    6. -- Requirements : Access to the ALL views.
    7. -- Call Syntax  : @object_status (schema-name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET SERVEROUTPUT ON
    11. SET PAGESIZE 1000
    12. SET LINESIZE 255
    13. SET FEEDBACK OFF
    14. SET VERIFY OFF
    15.  
    16. SELECT Substr(object_name,1,30) object_name,
    17.        object_type,
    18.        status
    19. FROM   all_objects
    20. WHERE  owner = Upper('&&1');
    21.  
    22. PROMPT
    23. SET FEEDBACK ON
    24. SET PAGESIZE 18
    25. [oracle@MaxwellDBA monitoring]$

    16.objects.sql

    -- Description  : Displays information about all database objects.

    1. [oracle@MaxwellDBA monitoring]$ cat objects.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/objects.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all database objects.
    6. -- Requirements : Access to the dba_objects view.
    7. -- Call Syntax  : @objects [ object-name | % (for all)]
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINZESIZE 200 VERIFY OFF
    11.  
    12. COLUMN owner FORMAT A20
    13. COLUMN object_name FORMAT A30
    14. COLUMN edition_name FORMAT A15
    15.  
    16.  
    17. SELECT owner,
    18.        object_name,
    19.        -- subobject_name,
    20.        object_id,
    21.        data_object_id,
    22.        object_type,
    23.        TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
    24.        TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') AS last_ddl_time,
    25.        timestamp,
    26.        status,
    27.        temporary,
    28.        generated,
    29.        secondary,
    30.        --namespace,
    31.        edition_name
    32. FROM dba_objects
    33. WHERE UPPER(object_name) LIKE UPPER('%&1%')
    34. ORDER BY owner,object_name;
    35.  
    36. SET VERIFY ON
    37. [oracle@MaxwellDBA monitoring]$

    17.statistics_prefs.sql

    -- Description  : Displays current statistics preferences.

    1. [oracle@MaxwellDBA monitoring]$ cat statistics_prefs.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/statistics_prefs.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays current statistics preferences.
    6. -- Requirements : Access to the DBMS_STATS package.
    7. -- Call Syntax  : @statistics_prefs
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SET LINESIZE 250
    12.  
    13. COLUMN autostats_target FORMAT A20
    14. COLUMN cascade FORMAT A25
    15. COLUMN degree FORMAT A10
    16. COLUMN estimate_percent FORMAT A30
    17. COLUMN method_opt FORMAT A25
    18. COLUMN no_invalidate FORMAT A30
    19. COLUMN granularity FORMAT A15
    20. COLUMN publish FORMAT A10
    21. COLUMN incremental FORMAT A15
    22. COLUMN stale_percent FORMAT A15
    23.  
    24. SELECT DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') AS autostats_target,
    25.        DBMS_STATS.GET_PREFS('CASCADE') AS cascade,
    26.        DBMS_STATS.GET_PREFS('DEGREE') AS degree,
    27.        DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') AS estimate_percent,
    28.        DBMS_STATS.GET_PREFS('METHOD_OPT') AS method_opt,
    29.        DBMS_STATS.GET_PREFS('NO_INVALIDATE') AS no_invalidate,
    30.        DBMS_STATS.GET_PREFS('GRANULARITY') AS granularity,
    31.        DBMS_STATS.GET_PREFS('PUBLISH') AS publish,
    32.        DBMS_STATS.GET_PREFS('INCREMENTAL') AS incremental,
    33.        DBMS_STATS.GET_PREFS('STALE_PERCENT') AS stale_percent
    34. FROM   dual;
    35. [oracle@MaxwellDBA monitoring]$ 
    1.  
    2. SQL> @/home/oracle/oracledba/monitoring/statistics_prefs.sql
    3.  
    4. AUTOSTATS_TARGET     CASCADE                   DEGREE     ESTIMATE_PERCENT               METHOD_OPT                NO_INVALIDATE                  GRANULARITY     PUBLISH    INCREMENTAL     STALE_PERCENT
    5. -------------------- ------------------------- ---------- ------------------------------ ------------------------- ------------------------------ --------------- ---------- --------------- ---------------
    6. AUTO                 DBMS_STATS.AUTO_CASCADE   NULL       DBMS_STATS.AUTO_SAMPLE_SIZE    FOR ALL COLUMNS SIZE AUTO DBMS_STATS.AUTO_INVALIDATE     AUTO            TRUE           FALSE           10
    7.  
    8. 1 row selected.
    9.  
    10. SQL>

    18.synonyms_to_missing_objects.sql

    -- Description  : Lists all synonyms that point to missing objects.

    1. [oracle@MaxwellDBA monitoring]$ cat synonyms_to_missing_objects.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/synonyms_to_missing_objects.sql
    4. -- Author       : Maxwell
    5. -- Description  : Lists all synonyms that point to missing objects.
    6. -- Call Syntax  : @synonyms_to_missing_objects(object-schema-name or all)
    7. -- Requirements : Access to the DBA views.
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 1000 VERIFY OFF
    11.  
    12. SELECT s.owner,
    13.        s.synonym_name,
    14.        s.table_owner, 
    15.        s.table_name
    16. FROM   dba_synonyms s
    17. WHERE  s.db_link IS NULL
    18. AND    s.table_owner NOT IN ('SYS','SYSTEM')
    19. AND    NOT EXISTS (SELECT 1
    20.                    FROM dba_objects o
    21.                    WHERE o.owner = s.table_owner
    22.                    AND o.object_name = s.table_name
    23.                    AND o.object_type != 'SYNONYM')
    24. AND s.table_owner = DECODE(UPPER('&1'), 'ALL', s.table_owner, UPPER('&1'))
    25. ORDER BY s.owner, s.synonym_name;
    26.  
    27. SET LINESIZE 80 VERIFY ON
    28. [oracle@MaxwellDBA monitoring]$

    19.system_events.sql

    -- Description  : Displays information on all system events.

    1. [oracle@MaxwellDBA monitoring]$ cat system_events.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/system_events.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information on all system events.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @system_events
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SELECT event,
    11.        total_waits,
    12.        total_timeouts,
    13.        time_waited,
    14.        average_wait,
    15.        time_waited_micro
    16. FROM v$system_event
    17. ORDER BY event;
    18. [oracle@MaxwellDBA monitoring]$

    20.system_parameters.sql

    -- Description  : Displays a list of all the system parameters.

    1. [oracle@MaxwellDBA monitoring]$ cat system_parameters.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/system_parameters.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of all the system parameters.
    6. --                Comment out isinstance_modifiable for use prior to 10g.
    7. -- Requirements : Access to the v$ views.
    8. -- Call Syntax  : @system_parameters
    9. -- Last Modified: 05-AUG-2022
    10. -- -----------------------------------------------------------------------------------
    11. SET LINESIZE 500
    12.  
    13. COLUMN name  FORMAT A30
    14. COLUMN value FORMAT A60
    15.  
    16. SELECT sp.name,
    17.        sp.type,
    18.        sp.value,
    19.        sp.isses_modifiable,
    20.        sp.issys_modifiable,
    21.        sp.isinstance_modifiable
    22. FROM   v$system_parameter sp
    23. ORDER BY sp.name;
    24. [oracle@MaxwellDBA monitoring]$

    21.system_privs.sql

    -- Description  : Displays users granted the specified system privilege.

    1. [oracle@MaxwellDBA monitoring]$ cat system_privs.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/system_privs.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays users granted the specified system privilege.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @system_privs ("sys-priv")
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200 VERIFY OFF
    11.  
    12. SELECT privilege,
    13.        grantee,
    14.        admin_option
    15. FROM   dba_sys_privs
    16. WHERE  privilege LIKE UPPER('%&1%')
    17. ORDER BY privilege, grantee;
    18.  
    19. SET VERIFY ON
    20. [oracle@MaxwellDBA monitoring]$

    22.system_stats.sql

    -- Description  : Displays system statistics.

    1. [oracle@MaxwellDBA monitoring]$ cat system_stats.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/system_stats.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays system statistics.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @system_stats (statistic-name or all)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET VERIFY OFF
    11.  
    12. COLUMN name FORMAT A50
    13. COLUMN value FORMAT 99999999999999999999
    14.  
    15. SELECT sn.name, ss.value
    16. FROM   v$sysstat ss,
    17.        v$statname sn
    18. WHERE  ss.statistic# = sn.statistic#
    19. AND    sn.name LIKE '%' || DECODE(LOWER('&1'), 'all', '', LOWER('&1')) || '%';
    20. [oracle@MaxwellDBA monitoring]$

    23.table_dep.sql

    -- Description  : Displays a list dependencies for the specified table.

    1. [oracle@MaxwellDBA monitoring]$ cat table_dep.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/table_dep.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list dependencies for the specified table.
    6. -- Requirements : Access to the ALL views.
    7. -- Call Syntax  : @table_dep (table-name) (schema-name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. PROMPT
    11. SET VERIFY OFF
    12. SET FEEDBACK OFF
    13. SET LINESIZE 255
    14. SET PAGESIZE 1000
    15.  
    16.  
    17. SELECT ad.referenced_name "Object",
    18.        ad.name "Ref Object",
    19.        ad.type "Type",
    20.        Substr(ad.referenced_owner,1,10) "Ref Owner",
    21.        Substr(ad.referenced_link_name,1,20) "Ref Link Name"
    22. FROM   all_dependencies ad
    23. WHERE  ad.referenced_name = Upper('&&1')
    24. AND    ad.owner           = Upper('&&2')
    25. ORDER BY 1,2,3;
    26.  
    27. SET VERIFY ON
    28. SET FEEDBACK ON
    29. SET PAGESIZE 14
    30. PROMPT
    31. [oracle@MaxwellDBA monitoring]$

    24.table_extents.sql

    -- Description  : Displays a list of tables having more than 1 extent.

    1. [oracle@MaxwellDBA monitoring]$ cat table_extents.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/table_extents.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of tables having more than 1 extent.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @table_extents (schema-name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET VERIFY OFF
    13.  
    14. SELECT t.table_name,
    15.        Count(e.segment_name) extents,
    16.        t.max_extents,
    17.        t.num_rows "ROWS",
    18.        Trunc(t.initial_extent/1024) "INITIAL K",
    19.        Trunc(t.next_extent/1024) "NEXT K"
    20. FROM   all_tables t,
    21.        dba_extents e
    22. WHERE  e.segment_name = t.table_name
    23. AND    e.owner        = t.owner
    24. AND    t.owner        = Upper('&&1')
    25. GROUP BY t.table_name,
    26.          t.max_extents,
    27.          t.num_rows,
    28.          t.initial_extent,
    29.          t.next_extent
    30. HAVING   Count(e.segment_name) > 1
    31. ORDER BY Count(e.segment_name) DESC;
    32.  
    33. SET PAGESIZE 18
    34. SET VERIFY ON
    35. [oracle@MaxwellDBA monitoring]$

    25.table_growth.sql

    -- Description  : Displays information on all active database sessions.

    1. [oracle@MaxwellDBA monitoring]$ cat table_growth.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/table_growth.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information on all active database sessions.
    6. -- Requirements : Access to the DBA_HIST views. Diagnostics and Tuning license.
    7. -- Call Syntax  : @table_growth (schema-name) (table_name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. COLUMN object_name FORMAT A30
    11.  
    12. SELECT TO_CHAR(sn.begin_interval_time,'DD-MON-YYYY HH24:MM') AS begin_interval_time,
    13.        sso.object_name,
    14.        ss.space_used_total
    15. FROM   dba_hist_seg_stat ss,
    16.        dba_hist_seg_stat_obj sso,
    17.        dba_hist_snapshot sn
    18. WHERE  sso.owner = UPPER('&1')
    19. AND    sso.obj# = ss.obj#
    20. AND    sn.snap_id = ss.snap_id
    21. AND    sso.object_name LIKE UPPER('&2') || '%'
    22. ORDER BY sn.begin_interval_time;
    23. [oracle@MaxwellDBA monitoring]$

    26.table_indexes.sql

    -- Description  : Displays index-column information for the specified table.

    1. [oracle@MaxwellDBA monitoring]$ cat table_indexes.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/table_indexes.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays index-column information for the specified table.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @table_indexes (schema-name) (table-name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500 PAGESIZE 1000 VERIFY OFF
    11.  
    12. COLUMN index_name      FORMAT A30
    13. COLUMN column_name     FORMAT A30
    14. COLUMN column_position FORMAT 99999
    15.  
    16. SELECT a.index_name,
    17.        a.column_name,
    18.        a.column_position
    19. FROM   all_ind_columns a,
    20.        all_indexes b
    21. WHERE  b.owner      = UPPER('&1')
    22. AND    b.table_name = UPPER('&2')
    23. AND    b.index_name = a.index_name
    24. AND    b.owner      = a.index_owner
    25. ORDER BY 1,3;
    26.  
    27. SET PAGESIZE 18 VERIFY ON
    28. [oracle@MaxwellDBA monitoring]$

    27.table_partitions.sql

    -- Description  : Displays partition information for the specified table, or all tables.

    1. [oracle@MaxwellDBA monitoring]$ cat table_partitions.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/table_partitions.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays partition information for the specified table, or all tables.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @table_partitions (table-name or all) (schema-name)
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET FEEDBACK OFF
    13. SET VERIFY OFF
    14.  
    15. SELECT a.table_name,
    16.        a.partition_name,
    17.        a.tablespace_name,
    18.        a.initial_extent,
    19.        a.next_extent,
    20.        a.pct_increase,
    21.        a.num_rows,
    22.        a.avg_row_len
    23. FROM   dba_tab_partitions a
    24. WHERE  a.table_name  = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
    25. AND    a.table_owner = Upper('&&2')
    26. ORDER BY a.table_name, a.partition_name
    27. /
    28.  
    29.  
    30. SET PAGESIZE 14
    31. SET FEEDBACK ON
    32. [oracle@MaxwellDBA monitoring]$

    28.table_stats.sql

    -- Description  : Displays the table statistics belonging to the specified schema.

    1. [oracle@MaxwellDBA monitoring]$ cat table_stats.sql
    2. -- File Name    : /monitoring/table_stats.sql
    3. -- Author       : Maxwell
    4. -- Description  : Displays the table statistics belonging to the specified schema.
    5. -- Requirements : Access to the DBA and v$ views.
    6. -- Call Syntax  : @table_stats (schema-name) (table-name)
    7. -- Last Modified: 05-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET LINESIZE 300 VERIFY OFF
    10.  
    11. COLUMN owner FORMAT A20
    12. COLUMN table_name FORMAT A30
    13. COLUMN index_name FORMAT A30
    14.  
    15. SELECT owner,
    16.        table_name,
    17.        num_rows,
    18.        blocks,
    19.        empty_blocks,
    20.        avg_space
    21.        chain_cnt,
    22.        avg_row_len,
    23.        last_analyzed
    24. FROM   dba_tables
    25. WHERE  owner      = UPPER('&1')
    26. AND    table_name = UPPER('&2');
    27.  
    28. SELECT index_name,
    29.        blevel,
    30.        leaf_blocks,
    31.        distinct_keys,
    32.        avg_leaf_blocks_per_key,
    33.        avg_data_blocks_per_key,
    34.        clustering_factor,
    35.        num_rows,
    36.        last_analyzed
    37. FROM   dba_indexes
    38. WHERE  table_owner = UPPER('&1')
    39. AND    table_name  = UPPER('&2')
    40. ORDER BY index_name;
    41.  
    42. COLUMN column_name FORMAT A30
    43. COLUMN low_value FORMAT A40
    44. COLUMN high_value FORMAT A40
    45. COLUMN endpoint_actual_value FORMAT A30
    46.  
    47. SELECT column_id,
    48.        column_name,
    49.        num_distinct,
    50.        avg_col_len,
    51.        histogram,
    52.        low_value,
    53.        high_value
    54. FROM   dba_tab_columns
    55. WHERE  owner       = UPPER('&1')
    56. AND    table_name  = UPPER('&2')
    57. ORDER BY column_id;
    58.  
    59. SET VERIFY ON
    60. [oracle@MaxwellDBA monitoring]$

    29.table_triggers.sql

    -- Description  : Lists the triggers for the specified table.

    1. [oracle@MaxwellDBA monitoring]$ cat table_triggers.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/table_triggers.sql
    4. -- Author       : Maxwell
    5. -- Description  : Lists the triggers for the specified table.
    6. -- Call Syntax  : @table_triggers (schema) (table_name)
    7. -- Last Modified: 05-AUG-22
    8. -- -----------------------------------------------------------------------------------
    9. SELECT owner,
    10.        trigger_name,
    11.        status
    12. FROM   dba_triggers
    13. WHERE  table_owner = UPPER('&1')
    14. AND    table_name = UPPER('&2');
    15. [oracle@MaxwellDBA monitoring]$

    30.tables_with_locked_stats.sql

    -- Description  : Displays tables with locked stats.

    1. [oracle@MaxwellDBA monitoring]$ cat tables_with_locked_stats.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/tables_with_locked_stats.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays tables with locked stats.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @tables_with_locked_stats.sql
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SELECT owner,
    12.        table_name,
    13.        stattype_locked
    14. FROM   dba_tab_statistics
    15. WHERE  stattype_locked IS NOT NULL
    16. ORDER BY owner, table_name;
    17. [oracle@MaxwellDBA monitoring]$

    31.tables_with_zero_rows.sql

    -- Description  : Displays tables with stats saying they have zero rows.

    1. [oracle@MaxwellDBA monitoring]$ cat tables_with_zero_rows.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/tables_with_zero_rows.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays tables with stats saying they have zero rows.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @tables_with_zero_rows.sql
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SELECT owner,
    12.        table_name,
    13.        last_analyzed,
    14.        num_rows
    15. FROM   dba_tables
    16. WHERE  num_rows = 0
    17. AND    owner NOT IN ('SYS','SYSTEM','SYSMAN','XDB','MDSYS',
    18.                      'WMSYS','OUTLN','ORDDATA','ORDSYS',
    19.                      'OLAPSYS','EXFSYS','DBNSMP','CTXSYS',
    20.                      'APEX_030200','FLOWS_FILES','SCOTT',
    21.                      'TSMSYS','DBSNMP','APPQOSSYS','OWBSYS',
    22.                      'DMSYS','FLOWS_030100','WKSYS','WK_TEST')
    23. ORDER BY owner, table_name;
    24. [oracle@MaxwellDBA monitoring]$

    32.tablespace.sql

    -- Description  : Displays information about tablespaces.

    1. [oracle@MaxwellDBA monitoring]$ cat tablespaces.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/tablespaces.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about tablespaces.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @tablespaces
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SET LINESIZE 200
    12.  
    13. SELECT tablespace_name,
    14.        block_size,
    15.        extent_management,
    16.        allocation_type,
    17.        segment_space_management,
    18.        status
    19. FROM   dba_tablespaces
    20. ORDER BY tablespace_name;
    21. [oracle@MaxwellDBA monitoring]$

  • 相关阅读:
    数字藏品值得探究，依然是广阔的大海播
    Uboot启动学习笔记之四-uboot之初体验
    Mysql加密功能
    诚迈科技董事长王继平出席中国（太原）人工智能大会并发表演讲
    python贪心算法——以“修理牛棚”题目为例
    刷题记录:牛客NC201628纸牌游戏
    看了同事这10个IDEA神级插件，我也悄悄安装了
    深入理解java虚拟机-1.自动内存管理
    Tekton — 通过tekton-operator部署tekton组件
    常见的23种设计模式总结
  • 原文地址：https://blog.csdn.net/u011868279/article/details/126170247