• 灵活好用的sql monitoring 脚本 part5

    51.Script: directories.sql

    -- Description  : Displays information about all directories.

    1. [oracle@MaxwellDBA monitoring]$ cat directories.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/directories.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all directories.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @directories
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 150
    11.  
    12. COLUMN owner FORMAT A20
    13. COLUMN directory_name FORMAT A25
    14. COLUMN directory_path FORMAT A80
    15.  
    16. SELECT *
    17. FROM   dba_directories
    18. ORDER BY owner, directory_name;
    19. [oracle@MaxwellDBA monitoring]$

    52.Script: directory_permissions.sql

    -- Description  : Displays permission information about all directories.

    1. [oracle@MaxwellDBA monitoring]$ cat directory_permissions.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/directory_permissions.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays permission information about all directories.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @directory_permissions (directory_name)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200
    11.  
    12. COLUMN grantee   FORMAT A20
    13. COLUMN owner     FORMAT A10
    14. COLUMN grantor   FORMAT A20
    15. COLUMN privilege FORMAT A20
    16.  
    17. SELECT * 
    18. FROM   dba_tab_privs 
    19. WHERE  table_name = UPPER('&1');
    20. [oracle@MaxwellDBA monitoring]$

    53.Script: dispatchers.sql

    -- Description  : Displays dispatcher statistics.

    1. [oracle@MaxwellDBA monitoring]$ cat dispatchers.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/dispatchers.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays dispatcher statistics.
    6. -- Requirements : Access to the v$ views.
    7. -- Call Syntax  : @dispatchers
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET VERIFY OFF
    13.  
    14. SELECT a.name "Name",
    15.        a.status "Status",
    16.        a.accept "Accept",
    17.        a.messages "Total Mesgs",
    18.        a.bytes "Total Bytes",
    19.        a.owned "Circs Owned",
    20.        a.idle "Total Idle Time",
    21.        a.busy "Total Busy Time",
    22.        Round(a.busy/(a.busy + a.idle),2) "Load"
    23. FROM   v$dispatcher a
    24. ORDER BY 1;
    25.  
    26. SET PAGESIZE 14
    27. SET VERIFY ON
    28. [oracle@MaxwellDBA monitoring]$

    54.Script: error_stack.sql

    -- Description  : Displays contents of the error stack.

    1. [oracle@MaxwellDBA monitoring]$ cat error_stack.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/error_stack.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays contents of the error stack.
    6. -- Call Syntax  : @error_stack
    7. -- Last Modified: 06-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET SERVEROUTPUT ON
    10. DECLARE
    11.   v_stack  VARCHAR2(2000);
    12. BEGIN
    13.           v_stack := Dbms_Utility.Format_Error_Stack;
    14.           Dbms_Output.Put_Line(v_stack);
    15. END;
    16. /
    17. [oracle@MaxwellDBA monitoring]$
    1. SQL> 
    2. SQL> @/home/oracle/oracledba/monitoring/error_stack.sql 
    3.  
    4. PL/SQL procedure successfully completed.
    5.  
    6. SQL>

    55.Script: errors.sql

    -- Description  : Displays the source line and the associated error after compilation failure.

    1. [oracle@MaxwellDBA monitoring]$ cat errors.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/errors.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the source line and the associated error after compilation failure.
    6. -- Comments     : Essentially the same as SHOW ERRORS.
    7. -- Call Syntax  : @errors (source-name)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SELECT To_Char(a.line) || ' - ' || a.text error
    11. FROM   user_source a,
    12.        user_errors b
    13. WHERE  a.name = Upper('&&1')
    14. AND    a.name = b.name
    15. AND    a.type = b.type
    16. AND    a.line = b.line
    17. ORDER BY a.name, a.line;
    18. [oracle@MaxwellDBA monitoring]$

    56.Script: explain.sql

    -- Description  : Displays a tree-style execution plan of the specified statement after it has been explained

    1. [oracle@MaxwellDBA monitoring]$ cat explain.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/explain.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a tree-style execution plan of the specified statement after it has been explained.
    6. -- Requirements : Access to the plan table.
    7. -- Call Syntax  : @explain (statement-id)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET PAGESIZE 100
    11. SET LINESIZE 200
    12. SET VERIFY OFF
    13.  
    14. COLUMN plan             FORMAT A50
    15. COLUMN object_name      FORMAT A30
    16. COLUMN object_type      FORMAT A15
    17. COLUMN bytes            FORMAT 9999999999
    18. COLUMN cost             FORMAT 9999999
    19. COLUMN partition_start  FORMAT A20
    20. COLUMN partition_stop   FORMAT A20
    21.  
    22. SELECT LPAD(' ', 2 * (level - 1)) ||
    23.        DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
    24.        INITCAP(pt.operation) ||
    25.        DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
    26.        pt.object_name,
    27.        pt.object_type,
    28.        pt.bytes,
    29.        pt.cost,
    30.        pt.partition_start,
    31.        pt.partition_stop
    32. FROM   plan_table pt
    33. START WITH pt.id = 0
    34.   AND pt.statement_id = '&1'
    35. CONNECT BY PRIOR pt.id = pt.parent_id
    36.   AND pt.statement_id = '&1';
    37. [oracle@MaxwellDBA monitoring]$

    57.Script: file_io.sql

    1. [oracle@MaxwellDBA monitoring]$ cat file_io.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/file_io.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the amount of IO for each datafile.
    6. -- Requirements : Access to the v$ views.
    7. -- Call Syntax  : @file_io
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET PAGESIZE 1000
    11.  
    12. SELECT Substr(d.name,1,50) "File Name",
    13.        f.phyblkrd "Blocks Read",
    14.        f.phyblkwrt "Blocks Writen",
    15.        f.phyblkrd + f.phyblkwrt "Total I/O"
    16. FROM   v$filestat f,
    17.        v$datafile d
    18. WHERE  d.file# = f.file#
    19. ORDER BY f.phyblkrd + f.phyblkwrt DESC;
    20.  
    21. SET PAGESIZE 18
    22. [oracle@MaxwellDBA monitoring]$

    58.Script: fk_columns.sql

    -- Description  : Displays information on all FKs for the specified schema and table.

    1. [oracle@MaxwellDBA monitoring]$ cat fk_columns.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/fk_columns.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information on all FKs for the specified schema and table.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @fk_columns (schema-name or all) (table-name or all)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET VERIFY OFF
    11. SET LINESIZE 1000
    12. COLUMN column_name FORMAT A30
    13. COLUMN r_column_name FORMAT A30
    14.  
    15. SELECT c.constraint_name,
    16.        cc.table_name,
    17.        cc.column_name,
    18.        rcc.table_name AS r_table_name,
    19.        rcc.column_name AS r_column_name,
    20.        cc.position
    21. FROM   dba_constraints c
    22.        JOIN dba_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
    23.        JOIN dba_cons_columns rcc ON c.owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name AND cc.position = rcc.position
    24. WHERE  c.owner      = DECODE(UPPER('&1'), 'ALL', c.owner, UPPER('&1'))
    25. AND    c.table_name = DECODE(UPPER('&2'), 'ALL', c.table_name, UPPER('&2'))
    26. ORDER BY c.constraint_name, cc.table_name, cc.position;
    27. [oracle@MaxwellDBA monitoring]$

    59.Script: fks.sql

    -- Description  : Displays the constraints on a specific table and those referencing it.

    1. [oracle@MaxwellDBA monitoring]$ cat fks.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/fks.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the constraints on a specific table and those referencing it.
    6. -- Call Syntax  : @fks (table-name) (schema)
    7. -- Last Modified: 06-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. PROMPT
    10. SET VERIFY OFF
    11. SET FEEDBACK OFF
    12. SET LINESIZE 255
    13. SET PAGESIZE 1000
    14.  
    15. PROMPT
    16. PROMPT Constraints Owned By Table
    17. PROMPT ==========================
    18. SELECT c.constraint_name "Constraint",
    19.        Decode(c.constraint_type,'P','Primary Key',
    20.                                 'U','Unique Key',
    21.                                 'C','Check',
    22.                                 'R','Foreign Key',
    23.                                  c.constraint_type) "Type",
    24.        c.r_owner "Ref Table",
    25.        c.r_constraint_name "Ref Constraint"
    26. FROM   all_constraints c
    27. WHERE  c.table_name = Upper('&&1')
    28. AND    c.owner      = Upper('&&2');
    29.  
    30.  
    31. PROMPT
    32. PROMPT Constraints Referencing Table
    33. PROMPT =============================
    34. SELECT c1.table_name "Table",
    35.        c1.constraint_name "Foreign Key",
    36.        c1.r_constraint_name "References"
    37. FROM   all_constraints c1 
    38. WHERE  c1.owner      = Upper('&&2')
    39. AND    c1.r_constraint_name IN (SELECT c2.constraint_name
    40.                                 FROM   all_constraints c2                                                                                                              WHERE  c2.table_name = Upper('&&1')
    41.                                 AND    c2.owner      = Upper('&&2')
    42.                                 AND    c2.constraint_type IN ('P','U'));
    43.  
    44.  
    45. SET VERIFY ON
    46. SET FEEDBACK ON
    47. SET PAGESIZE 1000
    48. PROMPT
    49. [oracle@MaxwellDBA monitoring]$

     

    60.Script: free_space.sql

    -- Description  : Displays space usage for each datafile.

    1. [oracle@MaxwellDBA monitoring]$ cat free_space.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/free_space.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays space usage for each datafile.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @free_space
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET PAGESIZE 100
    11. SET LINESIZE 265
    12.  
    13. COLUMN tablespace_name FORMAT A20
    14. COLUMN file_name FORMAT A50
    15.  
    16. SELECT df.tablespace_name,
    17.        df.file_name,
    18.        df.size_mb,
    19.        f.free_mb,
    20.        df.max_size_mb,
    21.        f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb,
    22.        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
    23. FROM   (SELECT file_id,
    24.                file_name,
    25.                tablespace_name,
    26.                TRUNC(bytes/1024/1024) AS size_mb,
    27.                TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
    28.         FROM   dba_data_files) df,
    29.         (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
    30.                 file_id
    31.          FROM dba_free_space
    32.          GROUP BY file_id) f
    33. WHERE  df.file_id = f.file_id (+)
    34. ORDER BY df.tablespace_name,
    35.          df.file_name;
    36.  
    37. PROMPT
    38. SET PAGESIZE 14
    39. [oracle@MaxwellDBA monitoring]$

    61.Script: health.sql

    -- Description  : Lots of information about the database so you can asses the general health of the system.

    1. [oracle@MaxwellDBA monitoring]$ cat health.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/health.sql
    4. -- Author       : Maxwell
    5. -- Description  : Lots of information about the database so you can asses the general health of the system.
    6. -- Requirements : Access to the V$ & DBA views and several other monitoring scripts.
    7. -- Call Syntax  : @health (username/password@service)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SPOOL Health_Checks.txt
    11.  
    12. conn &1
    13. @db_info
    14. @sessions
    15. @ts_full
    16. @max_extents
    17.  
    18. SPOOL OFF
    19. [oracle@MaxwellDBA monitoring]$

    62.Script: hidden_parameters.sql

    -- Description  : Displays a list of one or all the hidden parameters.

    1. [oracle@MaxwellDBA monitoring]$ cat hidden_parameters.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/hidden_parameters.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of one or all the hidden parameters.
    6. -- Requirements : Access to the v$ views.
    7. -- Call Syntax  : @hidden_parameters (parameter-name or all)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET VERIFY OFF
    11. COLUMN parameter      FORMAT a37
    12. COLUMN description    FORMAT a30 WORD_WRAPPED
    13. COLUMN session_value  FORMAT a10
    14. COLUMN instance_value FORMAT a10
    15.  
    16. SELECT a.ksppinm AS parameter,
    17.        a.ksppdesc AS description,
    18.        b.ksppstvl AS session_value,
    19.        c.ksppstvl AS instance_value
    20. FROM   x$ksppi a,
    21.        x$ksppcv b,
    22.        x$ksppsv c
    23. WHERE  a.indx = b.indx
    24. AND    a.indx = c.indx
    25. AND    a.ksppinm LIKE '/_%' ESCAPE '/'
    26. AND    a.ksppinm = DECODE(LOWER('&1'), 'all', a.ksppinm, LOWER('&1'))
    27. ORDER BY a.ksppinm;
    28. [oracle@MaxwellDBA monitoring]$

    63.Script: high_water_mark.sql

    -- Description  : Displays the High Water Mark for the specified table, or all tables.

    1. [oracle@MaxwellDBA monitoring]$ cat high_water_mark.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/high_water_mark.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the High Water Mark for the specified table, or all tables.
    6. -- Requirements : Access to the Dbms_Space.
    7. -- Call Syntax  : @high_water_mark (table_name or all) (schema-name)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET SERVEROUTPUT ON
    11. SET VERIFY OFF
    12.  
    13. DECLARE
    14.   CURSOR cu_tables IS
    15.     SELECT a.owner,
    16.            a.table_name
    17.     FROM   all_tables a
    18.     WHERE  a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
    19.     AND    a.owner      = Upper('&&2');
    20.  
    21.   op1  NUMBER;
    22.   op2  NUMBER;
    23.   op3  NUMBER;
    24.   op4  NUMBER;
    25.   op5  NUMBER;
    26.   op6  NUMBER;
    27.   op7  NUMBER;
    28. BEGIN
    29.  
    30.    Dbms_Output.Disable;
    31.    Dbms_Output.Enable(1000000);
    32.    Dbms_Output.Put_Line('TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK');
    33.    Dbms_Output.Put_Line('------------------------------  ---------------  ---------------  ---------------');
    34.    FOR cur_rec IN cu_tables LOOP
    35.      Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
    36.      Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
    37.                           LPad(op3,15,' ')                ||
    38.                           LPad(op1,15,' ')                ||
    39.                           LPad(Trunc(op1-op3-1),15,' ')); 
    40.    END LOOP;
    41.  
    42. END;
    43. /
    44.  
    45. SET VERIFY ON
    46. [oracle@MaxwellDBA monitoring]$

    64.Script: hot_blocks.sql

    -- Description  : Detects hot blocks.

    1. -- -----------------------------------------------------------------------------------
    2. -- File Name    : /monitoring/hot_blocks.sql
    3. -- Author       : Maxwell
    4. -- Description  : Detects hot blocks.
    5. -- Call Syntax  : @hot_blocks
    6. -- Last Modified: 06-AUG-2022
    7. -- -----------------------------------------------------------------------------------
    8. SET LINESIZE 200
    9. SET VERIFY OFF
    10.  
    11. SELECT *
    12. FROM   (SELECT name,
    13.                addr,
    14.                gets,
    15.                misses,
    16.                sleeps
    17.         FROM   v$latch_children
    18.         WHERE  name = 'cache buffers chains'
    19.         AND    misses > 0
    20.         ORDER BY misses DESC)
    21. WHERE  rownum < 11;
    22.  
    23. ACCEPT address PROMPT "Enter ADDR: "
    24.  
    25. COLUMN owner FORMAT A15
    26. COLUMN object_name FORMAT A30
    27. COLUMN subobject_name FORMAT A20
    28.  
    29. SELECT *
    30. FROM   (SELECT o.owner,
    31.                o.object_name,
    32.                o.subobject_name,
    33.                bh.tch,
    34.                bh.obj,
    35.                bh.file#,
    36.                bh.dbablk,
    37.                bh.class,
    38.                bh.state
    39.         FROM   x$bh bh,
    40.                dba_objects o
    41.         WHERE  o.data_object_id = bh.obj
    42.         AND    hladdr = '&address'
    43.         ORDER BY tch DESC)
    44. WHERE  rownum < 11;

    65.Script: identify_trace_file.sql

    -- Description  : Displays the name of the trace file associated with the current session.

    1. [oracle@MaxwellDBA monitoring]$ cat identify_trace_file.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/identify_trace_file.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the name of the trace file associated with the current session.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @identify_trace_file
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 100
    11. COLUMN trace_file FORMAT A60
    12.  
    13. SELECT s.sid,
    14.        s.serial#,
    15.        pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
    16.        '_ora_' || p.spid || '.trc' AS trace_file
    17. FROM   v$session s,
    18.        v$process p,
    19.        v$parameter pa
    20. WHERE  pa.name = 'user_dump_dest'
    21. AND    s.paddr = p.addr
    22. AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
    23. [oracle@MaxwellDBA monitoring]$

    66.Script: index_extents.sql

    -- Description  : Displays number of extents for all indexes belonging to the specified table, or all tables.

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

    67.Script: index_monitoring_status.sql

    -- Description  : Shows the monitoring status for the specified table indexes.

    1. [oracle@MaxwellDBA monitoring]$ cat index_monitoring_status.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/index_monitoring_status.sql
    4. -- Author       : Maxwell
    5. -- Description  : Shows the monitoring status for the specified table indexes.
    6. -- Call Syntax  : @index_monitoring_status (table-name) (index-name or all)
    7. -- Last Modified: 06-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET VERIFY OFF
    10.  
    11. SELECT table_name,
    12.        index_name,
    13.        monitoring
    14. FROM   v$object_usage
    15. WHERE  table_name = UPPER('&1')
    16. AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
    17. [oracle@MaxwellDBA monitoring]$

    68.Script: index_partitions.sql

    -- Description  : Displays partition information for the specified index, or all indexes.

    1. [oracle@MaxwellDBA monitoring]$ cat index_partitions.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/index_partitions.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays partition information for the specified index, or all indexes.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @index_patitions (index_name or all) (schema-name)
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 500
    11. SET PAGESIZE 1000
    12. SET FEEDBACK OFF
    13. SET VERIFY OFF
    14.  
    15. SELECT a.index_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. FROM   dba_ind_partitions a
    23. WHERE  a.index_name  = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
    24. AND    a.index_owner = Upper('&&2')
    25. ORDER BY a.index_name, a.partition_name
    26. /
    27.  
    28. PROMPT
    29. SET PAGESIZE 14
    30. SET FEEDBACK ON
    31. [oracle@MaxwellDBA monitoring]$

    69.Script: index_usage.sql

    -- Description  : Shows the usage for the specified table indexes.

    1. [oracle@MaxwellDBA monitoring]$ cat index_usage.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/index_usage.sql
    4. -- Author       : Maxwell
    5. -- Description  : Shows the usage for the specified table indexes.
    6. -- Call Syntax  : @index_usage (table-name) (index-name or all)
    7. -- Last Modified: 06-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET VERIFY OFF
    10. SET LINESIZE 200
    11.  
    12. SELECT table_name,
    13.        index_name,
    14.        used,
    15.        start_monitoring,
    16.        end_monitoring
    17. FROM   v$object_usage
    18. WHERE  table_name = UPPER('&1')
    19. AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));
    20. [oracle@MaxwellDBA monitoring]$

    70.Script: invalid_objects.sql

    -- Description  : Lists all invalid objects in the database.

    1. [oracle@MaxwellDBA monitoring]$ cat invalid_objects.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/invalid_objects.sql
    4. -- Author       : Maxwell
    5. -- Description  : Lists all invalid objects in the database.
    6. -- Call Syntax  : @invalid_objects
    7. -- Requirements : Access to the DBA views.
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. COLUMN owner FORMAT A30
    11. COLUMN object_name FORMAT A30
    12.  
    13. SELECT owner,
    14.        object_type,
    15.        object_name,
    16.        status
    17. FROM   dba_objects
    18. WHERE  status = 'INVALID'
    19. ORDER BY owner, object_type, object_name;
    20. [oracle@MaxwellDBA monitoring]$
  • 相关阅读:
    事务码ABAVN-资产卡片报废-BAPI_ASSET_RETIREMENT_POST
    java计算机毕业设计招聘信息系统源程序+mysql+系统+lw文档+远程调试
    python多线程系列—线程池ThreadPoolExecutor(八)
    [C#] 允许当前应用程序通过防火墙
    Django-中间件(切面编程AOP)
    SpringCloud搭建保姆级教程
    第 1 课 Python 的输出！
    排序算法：选择排序（直接选择排序、堆排序）
    易基因｜干货：m6A RNA甲基化MeRIP-seq测序分析实验全流程解析
    CSS阴影优化气泡框样式
  • 原文地址：https://blog.csdn.net/u011868279/article/details/126199066