• 灵活好用的sql monitoring 脚本 part4

    33.Script: column_defaults.sql

    -- Description  : Displays the default values where present for the specified table.

    1. [oracle@MaxwellDBA monitoring]$ cat column_defaults.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/column_defaults.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the default values where present for the specified table.
    6. -- Call Syntax  : @column_defaults (table-name)
    7. -- Last Modified: 06-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. SET LINESIZE 100
    10. SET VERIFY OFF
    11.  
    12. SELECT a.column_name "Column",
    13.        a.data_default "Default"
    14. FROM   all_tab_columns a
    15. WHERE  a.table_name = Upper('&1')
    16. AND    a.data_default IS NOT NULL
    17. /
    18. [oracle@MaxwellDBA monitoring]$ pwd
    19. /home/oracle/oracledba/monitoring
    20. [oracle@MaxwellDBA monitoring]$

    34.Script: controlfiles.sql

    -- Description  : Displays information about controlfiles.

    1. [oracle@MaxwellDBA monitoring]$ cat controlfiles.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/controlfiles.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about controlfiles.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @controlfiles
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SET LINESIZE 100
    12. COLUMN name FORMAT A80
    13.  
    14. SELECT name,
    15.        status
    16. FROM   v$controlfile
    17. ORDER BY name;
    18.  
    19. SET LINESIZE 80
    20. [oracle@MaxwellDBA monitoring]$
    1.  
    2. SQL> @/home/oracle/oracledba/monitoring/controlfiles.sql 
    3.  
    4. NAME                                                                             STATUS
    5. -------------------------------------------------------------------------------- -------
    6. /opt/oracle/oradata/ORCLCDB/control01.ctl
    7. /opt/oracle/oradata/ORCLCDB/control02.ctl
    8.  
    9. SQL>

    35.Script: datafiles.sql

    -- Description  : Displays information about datafiles.

    1. [oracle@MaxwellDBA monitoring]$ cat datafiles.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/datafiles.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about datafiles.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @datafiles
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SET LINESIZE 200
    12. COLUMN file_name FORMAT A70
    13.  
    14. SELECT file_id,
    15.        file_name,
    16.        ROUND(bytes/1024/1024/1024) AS size_gb,
    17.        ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
    18.        autoextensible,
    19.        increment_by,
    20.        status
    21. FROM   dba_data_files
    22. ORDER BY file_name;
    23. [oracle@MaxwellDBA monitoring]$ 
    1.  
    2. SQL> @/home/oracle/oracledba/monitoring/datafiles.sql 
    3.  
    4.    FILE_ID FILE_NAME                                                                 SIZE_GB MAX_SIZE_GB AUT INCREMENT_BY STATUS
    5. ---------- ---------------------------------------------------------------------- ---------- ----------- --- ------------ ---------
    6.          3 /opt/oracle/oradata/ORCLCDB/sysaux01.dbf                                        1          32 YES         1280 AVAILABLE
    7.          1 /opt/oracle/oradata/ORCLCDB/system01.dbf                                        1          32 YES         1280 AVAILABLE
    8.          4 /opt/oracle/oradata/ORCLCDB/undotbs01.dbf                                       0          32 YES          640 AVAILABLE
    9.          7 /opt/oracle/oradata/ORCLCDB/users01.dbf                                         0          32 YES          160 AVAILABLE
    10.  
    11. SQL>

    36.Script: open_cursors.sql

    -- Description  : Displays a list of all cursors currently open.

    1. [oracle@MaxwellDBA monitoring]$ cat open_cursors.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/open_cursors.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of all cursors currently open.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @open_cursors
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SELECT a.user_name,
    11.        a.sid,
    12.        a.sql_text
    13. FROM   v$open_cursor a
    14. ORDER BY 1,2
    15. /
    16. [oracle@MaxwellDBA monitoring]$

    37.Script: open_cursors_by_sid.sql

    -- Description  : Displays the SQL statement held for a specific SID.

    1. [oracle@MaxwellDBA monitoring]$ cat open_cursors_by_sid.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/open_cursors_by_sid.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the SQL statement held for a specific SID.
    6. -- Comments     : The SID can be found by running session.sql or top_session.sql.
    7. -- Requirements : Access to the V$ views.
    8. -- Call Syntax  : @open_cursors_by_sid (sid)
    9. -- Last Modified: 05-AUG-2022
    10. -- -----------------------------------------------------------------------------------
    11. SET LINESIZE 500
    12. SET PAGESIZE 1000
    13. SET VERIFY OFF
    14.  
    15. SELECT oc.sql_text, cursor_type
    16. FROM   v$open_cursor oc
    17. WHERE  oc.sid = &1
    18. ORDER BY cursor_type;
    19.  
    20. PROMPT
    21. SET PAGESIZE 14
    22. [oracle@MaxwellDBA monitoring]$
    1.  
    2. SQL> @/home/oracle/oracledba/monitoring/open_cursors_by_sid.sql 11 
    3.  
    4. SQL_TEXT                                                     CURSOR_TYPE
    5. ------------------------------------------------------------ ----------------------------------------------------------------
    6. insert into smon_scn_time (thread, time_mp, time_dp, scn, sc SESSION CURSOR CACHED
    7. delete from smon_scn_time where scn =  (select min(scn) from SESSION CURSOR CACHED
    8. select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t SESSION CURSOR CACHED
    9. update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn SESSION CURSOR CACHED
    10. select max(RETENTION) from SYS_FBA_FA                        SESSION CURSOR CACHED
    11.  
    12.  
    13. SQL>

    38.Script: open_cursors_full_by_sid.sql

    -- Description  : Displays the SQL statement held for a specific SID.

    1. [oracle@MaxwellDBA monitoring]$ cat open_cursors_full_by_sid.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/open_cursors_full_by_sid.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the SQL statement held for a specific SID.
    6. -- Comments     : The SID can be found by running session.sql or top_session.sql.
    7. -- Requirements : Access to the V$ views.
    8. -- Call Syntax  : @open_cursors_full_by_sid (sid)
    9. -- Last Modified: 05-AUG-2022
    10. -- -----------------------------------------------------------------------------------
    11. SET LINESIZE 500
    12. SET PAGESIZE 1000
    13. SET VERIFY OFF
    14.  
    15. SELECT st.sql_text
    16. FROM   v$sqltext st,
    17.        v$open_cursor oc
    18. WHERE  st.address = oc.address
    19. AND    st.hash_value = oc.hash_value
    20. AND    oc.sid = &1
    21. ORDER BY st.address, st.piece;
    22.  
    23. PROMPT
    24. SET PAGESIZE 14
    25. [oracle@MaxwellDBA monitoring]$ 
    1. SQL> @/home/oracle/oracledba/monitoring/open_cursors_full_by_sid.sql 11 
    2.  
    3. SQL_TEXT
    4. ----------------------------------------------------------------
    5. select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
    6. re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
    7. select max(RETENTION) from SYS_FBA_FA
    8. update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn_wrp
    9. =:4,  scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where scn =  (
    10. select min(scn) from smon_scn_time)
    11. delete from smon_scn_time where scn =  (select min(scn) from smo
    12. n_scn_time)
    13. insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wr
    14. p, scn_bas, num_mappings, tim_scn_map) values (0, :1, :2, :3, :4
    15. , :5, :6, :7)
    16.  
    17. 11 rows selected.
    18.  
    19.  
    20. SQL>

    39.Script: options.sql

    -- Description  : Displays information about all database options.

    1. [oracle@MaxwellDBA monitoring]$ cat options.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/options.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information about all database options.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @options
    8. -- Last Modified: 05-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. COLUMN value FORMAT A20
    12.  
    13. SELECT *
    14. FROM   v$option
    15. ORDER BY parameter;
    16. [oracle@MaxwellDBA monitoring]$ 
    1. SQL> @/home/oracle/oracledba/monitoring/options.sql 
    2.  
    3. PARAMETER                                                        VALUE                    CON_ID
    4. ---------------------------------------------------------------- -------------------- ----------
    5. ASM Proxy Instance                                               FALSE                         0
    6. Active Data Guard                                                TRUE                          0
    7. Adaptive Execution Plans                                         TRUE                          0
    8. Advanced Analytics                                               TRUE                          0
    9. Advanced Compression                                             TRUE                          0
    10. Advanced Index Compression                                       TRUE                          0
    11. Advanced replication                                             TRUE                          0
    12. Application Role                                                 TRUE                          0
    13. Automatic Data Optimization                                      TRUE                          0
    14. Automatic Storage Management                                     FALSE                         0
    15. Backup Encryption                                                TRUE                          0
    16.  
    17. PARAMETER                                                        VALUE                    CON_ID
    18. ---------------------------------------------------------------- -------------------- ----------
    19. Basic Compression                                                TRUE                          0
    20. Bit-mapped indexes                                               TRUE                          0
    21. Block Change Tracking                                            TRUE                          0
    22. Block Media Recovery                                             TRUE                          0
    23. Cache Fusion Lock Accelerator                                    TRUE                          0
    24. Centrally Managed User                                           TRUE                          0
    25. Change Data Capture                                              TRUE                          0
    26. Coalesce Index                                                   TRUE                          0
    27. Connection multiplexing                                          TRUE                          0
    28. Connection pooling                                               TRUE                          0
    29. Cross Transportable Backups                                      TRUE                          0
    30.  
    31. PARAMETER                                                        VALUE                    CON_ID
    32. ---------------------------------------------------------------- -------------------- ----------
    33. DICOM                                                            TRUE                          0
    34. Data Mining                                                      TRUE                          0
    35. Data Redaction                                                   TRUE                          0
    36. Database queuing                                                 TRUE                          0
    37. Database resource manager                                        TRUE                          0
    38. Deferred Segment Creation                                        TRUE                          0
    39. Duplexed backups                                                 TRUE                          0
    40. Enterprise User Security                                         TRUE                          0
    41. Exadata Discovery                                                TRUE                          0
    42. Export transportable tablespaces                                 TRUE                          0
    43. Fast-Start Fault Recovery                                        TRUE                          0
    44.  
    45. PARAMETER                                                        VALUE                    CON_ID
    46. ---------------------------------------------------------------- -------------------- ----------
    47. File Mapping                                                     TRUE                          0
    48. Fine-grained Auditing                                            TRUE                          0
    49. Fine-grained access control                                      TRUE                          0
    50. Flashback Data Archive                                           TRUE                          0
    51. Flashback Database                                               TRUE                          0
    52. Flashback Table                                                  TRUE                          0
    53. Global Data Services                                             TRUE                          0
    54. Heat Map                                                         TRUE                          0
    55. I/O Server                                                       FALSE                         0
    56. In-Memory Aggregation                                            TRUE                          0
    57. In-Memory Column Store                                           TRUE                          0
    58.  
    59. PARAMETER                                                        VALUE                    CON_ID
    60. ---------------------------------------------------------------- -------------------- ----------
    61. Incremental backup and recovery                                  TRUE                          0
    62. Instead-of triggers                                              TRUE                          0
    63. Java                                                             TRUE                          0
    64. Join index                                                       TRUE                          0
    65. Managed Standby                                                  TRUE                          0
    66. Management Database                                              FALSE                         0
    67. Materialized view rewrite                                        TRUE                          0
    68. OLAP                                                             TRUE                          0
    69. OLAP Window Functions                                            TRUE                          0
    70. Objects                                                          TRUE                          0
    71. Online Index Build                                               TRUE                          0
    72.  
    73. PARAMETER                                                        VALUE                    CON_ID
    74. ---------------------------------------------------------------- -------------------- ----------
    75. Online Redefinition                                              TRUE                          0
    76. Oracle Data Guard                                                TRUE                          0
    77. Oracle Database Vault                                            FALSE                         0
    78. Oracle Label Security                                            FALSE                         0
    79. Parallel backup and recovery                                     TRUE                          0
    80. Parallel execution                                               TRUE                          0
    81. Parallel load                                                    TRUE                          0
    82. Partitioning                                                     TRUE                          0
    83. Plan Stability                                                   TRUE                          0
    84. Point-in-time tablespace recovery                                TRUE                          0
    85. Privilege Analysis                                               TRUE                          0
    86.  
    87. PARAMETER                                                        VALUE                    CON_ID
    88. ---------------------------------------------------------------- -------------------- ----------
    89. Proxy authentication/authorization                               TRUE                          0
    90. Real Application Clusters                                        FALSE                         0
    91. Real Application Security                                        TRUE                          0
    92. Real Application Testing                                         TRUE                          0
    93. Result Cache                                                     TRUE                          0
    94. SQL Plan Management                                              TRUE                          0
    95. Sample Scan                                                      TRUE                          0
    96. SecureFiles Encryption                                           TRUE                          0
    97. Server Flash Cache                                               TRUE                          0
    98. Snapshot time recovery                                           TRUE                          0
    99. Spatial                                                          TRUE                          0
    100.  
    101. PARAMETER                                                        VALUE                    CON_ID
    102. ---------------------------------------------------------------- -------------------- ----------
    103. Streams Capture                                                  TRUE                          0
    104. Table Clustering                                                 TRUE                          0
    105. Transparent Application Failover                                 TRUE                          0
    106. Transparent Data Encryption                                      TRUE                          0
    107. Transparent Sensitive Data Protection                            TRUE                          0
    108. Trial Recovery                                                   TRUE                          0
    109. Unified Auditing                                                 FALSE                         0
    110. Unused Block Compression                                         TRUE                          0
    111. XStream                                                          TRUE                          0
    112. Zone Maps                                                        TRUE                          0
    113.  
    114. 87 rows selected.
    115.  
    116. SQL>

    40.Script: db_cache_advice.sql

    -- Description  : Predicts how changes to the buffer cache will affect physical reads.

    1. [oracle@MaxwellDBA monitoring]$ cat db_cache_advice.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/db_cache_advice.sql
    4. -- Author       : Maxwell
    5. -- Description  : Predicts how changes to the buffer cache will affect physical reads.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @db_cache_advice
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'
    12. COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'
    13. COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'
    14. COLUMN estd_physical_reads        FORMAT 999,999,999,999 heading 'Estd Phys| Reads'
    15.  
    16. SELECT size_for_estimate, 
    17.        buffers_for_estimate,
    18.        estd_physical_read_factor,
    19.        estd_physical_reads
    20. FROM   v$db_cache_advice
    21. WHERE  name          = 'DEFAULT'
    22. AND    block_size    = (SELECT value
    23.                         FROM   v$parameter
    24.                         WHERE  name = 'db_block_size')
    25. AND    advice_status = 'ON';
    26. [oracle@MaxwellDBA monitoring]$

    41.Script: db_info.sql

    -- Description  : Displays general information about the database.

    1. [oracle@MaxwellDBA monitoring]$ cat db_info.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/db_info.sql
    4. -- Author       : Maxwell 
    5. -- Description  : Displays general information about the database.
    6. -- Requirements : Access to the v$ views.
    7. -- Call Syntax  : @db_info
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET PAGESIZE 1000
    11. SET LINESIZE 100
    12. SET FEEDBACK OFF
    13.  
    14. SELECT *
    15. FROM   v$database;
    16.  
    17. SELECT *
    18. FROM   v$instance;
    19.  
    20. SELECT *
    21. FROM   v$version;
    22.  
    23. SELECT a.name,
    24.        a.value
    25. FROM   v$sga a;
    26.  
    27. SELECT Substr(c.name,1,60) "Controlfile",
    28.        NVL(c.status,'UNKNOWN') "Status"
    29. FROM   v$controlfile c
    30. ORDER BY 1;
    31.  
    32. SELECT Substr(d.name,1,60) "Datafile",
    33.        NVL(d.status,'UNKNOWN') "Status",
    34.        d.enabled "Enabled",
    35.        LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
    36. FROM   v$datafile d
    37. ORDER BY 1;
    38.  
    39. SELECT l.group# "Group",
    40.        Substr(l.member,1,60) "Logfile",
    41.        NVL(l.status,'UNKNOWN') "Status"
    42. FROM   v$logfile l
    43. ORDER BY 1,2;
    44.  
    45. PROMPT
    46. SET PAGESIZE 14
    47. SET FEEDBACK ON
    48. [oracle@MaxwellDBA monitoring]$
    1. SQL> @/home/oracle/oracledba/monitoring/db_info.sql 
    2.  
    3.       DBID NAME
    4. ---------- --------------------------------------------------------------------------------
    5. CREATED            RESETLOGS_CHANGE# RESETLOGS_TIME     PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TI
    6. ------------------ ----------------- ------------------ ----------------------- ------------------
    7. LOG_MODE     CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROL CONTROLFILE_CREATE CONTROLFILE_SEQUENCE#
    8. ------------ ------------------ --------------- ------- ------------------ ---------------------
    9. CONTROLFILE_CHANGE# CONTROLFILE_TIME   OPEN_RESETL VERSION_TIME       OPEN_MODE
    10. ------------------- ------------------ ----------- ------------------ --------------------
    11. PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A ACTIVATION# SWITCHOVER# DATABASE_ROLE
    12. -------------------- -------------------- -------- ----------- ----------- ----------------
    13. ARCHIVELOG_CHANGE# ARCHIVEL SWITCHOVER_STATUS    DATAGUAR GUARD_S SUPPLEME SUP SUP
    14. ------------------ -------- -------------------- -------- ------- -------- --- ---
    15. FORCE_LOGGING                           PLATFORM_ID
    16. --------------------------------------- -----------
    17. PLATFORM_NAME
    18. ----------------------------------------------------------------------------------------------------
    19. RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON       SUP SUP
    20. ---------------------------- ---------------------- ----------- ------------------ --- ---
    21. DB_UNIQUE_NAME                 STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_MODE    FS_FAILOVER_STATUS
    22. ------------------------------ -------------------------- ------------------- ----------------------
    23. FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD FS_FAIL
    24. ------------------------------ --------------------- -------
    25. FS_FAILOVER_OBSERVER_HOST
    26. ----------------------------------------------------------------------------------------------------
    27. CON PRIMARY_DB_UNIQUE_NAME         SUP MIN_REQUIRED_CAPTURE_CHANGE# CDB     CON_ID
    28. --- ------------------------------ --- ---------------------------- --- ----------
    29. PENDING_ROLE_CHANGE_TASKS
    30. ----------------------------------------------------------------------------------------------------
    31.   CON_DBID FOR SUP
    32. ---------- --- ---
    33. 2879679614 ORCLCDB
    34. 29-JUN-22                    1920977 29-JUN-22                                1 17-APR-19
    35. NOARCHIVELOG            6561295         6443856 CURRENT 29-JUN-22                         115474
    36.             6606029 06-AUG-22          NOT ALLOWED 29-JUN-22          READ WRITE
    37. MAXIMUM PERFORMANCE  UNPROTECTED          ENABLED   2879716734  2879716734 PRIMARY
    38.                  0 DISABLED NOT ALLOWED          DISABLED NONE    NO       NO  NO
    39. NO                                               13
    40. Linux x86 64-bit
    41.                            2                      2     6606060 NO                 NO  NO
    42. ORCLCDB                                                 0 DISABLED            DISABLED
    43.                                                    0
    44.  
    45. NO                                 NO                               YES          0
    46. NOT APPLICABLE
    47. 2879679614 NO  NO
    48.  
    49.  
    50. INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
    51. --------------- ---------------- ----------------------------------------------------------------
    52. VERSION           VERSION_LEGACY    VERSION_FULL      STARTUP_TIME       STATUS       PAR    THREAD#
    53. ----------------- ----------------- ----------------- ------------------ ------------ --- ----------
    54. ARCHIVE LOG_SWITCH_WAIT LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO     CON_ID
    55. ------- --------------- ---------- --- ----------------- ------------------ --------- --- ----------
    56. INSTANCE_MO EDITION FAMILY
    57. ----------- ------- --------------------------------------------------------------------------------
    58. DATABASE_TYPE
    59. ---------------
    60.               1 ORCLCDB          MaxwellDBA
    61. 19.0.0.0.0        19.0.0.0.0        19.3.0.0.0        29-JUN-22          OPEN         NO           1
    62. STOPPED                 ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO           0
    63. REGULAR     EE
    64. SINGLE
    65.  
    66.  
    67. BANNER
    68. --------------------------------------------------------------------------------
    69. BANNER_FULL
    70. ----------------------------------------------------------------------------------------------------
    71. BANNER_LEGACY                                                                        CON_ID
    72. -------------------------------------------------------------------------------- ----------
    73. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    74. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    75. Version 19.3.0.0.0
    76. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                    0
    77.  
    78.  
    79. NAME                                                                                  VALUE
    80. -------------------------------------------------------------------------------- ----------
    81. Fixed Size                                                                       ##########
    82. Variable Size                                                                    ##########
    83. Database Buffers                                                                 ##########
    84. Redo Buffers                                                                     ##########
    85.  
    86. Controlfile
    87. ----------------------------------------------------------------------------------------------------
    88. Status
    89. -------
    90. /opt/oracle/oradata/ORCLCDB/control01.ctl
    91. UNKNOWN
    92.  
    93. /opt/oracle/oradata/ORCLCDB/control02.ctl
    94. UNKNOWN
    95.  
    96.  
    97. Datafile
    98. ----------------------------------------------------------------------------------------------------
    99. Status  Enabled    Size (M)
    100. ------- ---------- ----------------------------------------
    101. /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
    102. ONLINE  READ WRITE      399.3
    103.  
    104. /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
    105. SYSTEM  READ WRITE      286.7
    106.  
    107. /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
    108. ONLINE  READ WRITE      102.4
    109.  
    110. /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
    111. ONLINE  READ WRITE        5.1
    112.  
    113. /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
    114. ONLINE  READ WRITE      337.9
    115.  
    116. /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
    117. SYSTEM  READ WRITE      276.4
    118.  
    119. /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
    120. ONLINE  READ WRITE      102.4
    121.  
    122. /opt/oracle/oradata/ORCLCDB/sysaux01.dbf
    123. ONLINE  READ WRITE     1310.7
    124.  
    125. /opt/oracle/oradata/ORCLCDB/system01.dbf
    126. SYSTEM  READ WRITE      962.5
    127.  
    128. /opt/oracle/oradata/ORCLCDB/undotbs01.dbf
    129. ONLINE  READ WRITE      302.0
    130.  
    131. /opt/oracle/oradata/ORCLCDB/users01.dbf
    132. ONLINE  READ WRITE        5.1
    133.  
    134.  
    135.      Group
    136. ----------
    137. Logfile
    138. ----------------------------------------------------------------------------------------------------
    139. Status
    140. -------
    141.          1
    142. /opt/oracle/oradata/ORCLCDB/redo01.log
    143. UNKNOWN
    144.  
    145.          2
    146. /opt/oracle/oradata/ORCLCDB/redo02.log
    147. UNKNOWN
    148.  
    149.          3
    150. /opt/oracle/oradata/ORCLCDB/redo03.log
    151. UNKNOWN
    152.  
    153.  
    154. SQL>

    42.Script: db_links.sql

    -- Description  : Displays information on all database links.

    1. [oracle@MaxwellDBA monitoring]$ cat db_links.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/db_links.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information on all database links.
    6. -- Requirements : Access to the DBA views.
    7. -- Call Syntax  : @db_links
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 150
    11.  
    12. COLUMN owner FORMAT A30
    13. COLUMN db_link FORMAT A30
    14. COLUMN username FORMAT A30
    15. COLUMN host FORMAT A30
    16.  
    17. SELECT owner,
    18.        db_link,
    19.        username,
    20.        host
    21. FROM   dba_db_links
    22. ORDER BY owner, db_link;
    23. [oracle@MaxwellDBA monitoring]$
    1. SQL> @/home/oracle/oracledba/monitoring/db_links.sql 
    2.  
    3. OWNER                          DB_LINK                        USERNAME                       HOST
    4. ------------------------------ ------------------------------ ------------------------------ ------------------------------
    5. SYS                            SYS_HUB                                                       SEEDDATA
    6.  
    7. 1 row selected.
    8.  
    9. SQL>

    43.Script: db_links_open.sql

    -- Description  : Displays information on all open database links.

    1. [oracle@MaxwellDBA monitoring]$ cat db_links_open.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/db_links_open.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays information on all open database links.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @db_links_open
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 200
    11.  
    12. COLUMN db_link FORMAT A30
    13.  
    14. SELECT db_link,
    15.        owner_id,
    16.        logged_on,
    17.        heterogeneous,
    18.        protocol,
    19.        open_cursors,
    20.        in_transaction,
    21.        update_sent,
    22.        commit_point_strength
    23. FROM   v$dblink
    24. ORDER BY db_link;
    25.  
    26. SET LINESIZE 80
    27. [oracle@MaxwellDBA monitoring]$

    44.Script: db_properties.sql

    -- Description  : Displays all database property values.

    1. [oracle@MaxwellDBA monitoring]$ cat db_properties.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/db_properties.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays all database property values.
    6. -- Call Syntax  : @db_properties
    7. -- Last Modified: 06-AUG-2022
    8. -- -----------------------------------------------------------------------------------
    9. COLUMN property_value FORMAT A50
    10.  
    11. SELECT property_name,
    12.        property_value
    13. FROM   database_properties
    14. ORDER BY property_name;
    15. [oracle@MaxwellDBA monitoring]$

    45.Script: param_valid_values.sql

    -- Description  : Lists all valid values for the specified parameter.

    1. [oracle@MaxwellDBA monitoring]$ cat param_valid_values.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/param_valid_values.sql
    4. -- Author       : Maxwell
    5. -- Description  : Lists all valid values for the specified parameter.
    6. -- Call Syntax  : @param_valid_values (parameter-name)
    7. -- Requirements : Access to the v$views.
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET VERIFY OFF
    11.  
    12. COLUMN value FORMAT A50
    13. COLUMN isdefault FORMAT A10
    14.  
    15. SELECT value,
    16.        isdefault
    17. FROM   v$parameter_valid_values
    18. WHERE  name = '&1';
    19. [oracle@MaxwellDBA monitoring]$ 
    1. SQL> @/home/oracle/oracledba/monitoring/param_valid_values.sql default_sharing 
    2.  
    3. VALUE                                              ISDEFAULT
    4. -------------------------------------------------- ----------
    5. NONE                                               FALSE
    6. METADATA                                           FALSE
    7. OBJECT                                             FALSE
    8. DATA                                               FALSE
    9. EXTENDED DATA                                      FALSE
    10.  
    11. 5 rows selected.
    12.  
    13. SQL>

    46.Script: parameter_diffs.sql

    -- Description  : Displays parameter values that differ between the current value and the spfile.

    1. [oracle@MaxwellDBA monitoring]$ cat parameter_diff.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/parameter_diffs.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays parameter values that differ between the current value and the spfile.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @parameter_diffs
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SET LINESIZE 120
    12. COLUMN name          FORMAT A30
    13. COLUMN current_value FORMAT A30
    14. COLUMN sid           FORMAT A8
    15. COLUMN spfile_value  FORMAT A30
    16.  
    17. SELECT p.name,
    18.        i.instance_name AS sid,
    19.        p.value AS current_value,
    20.        sp.sid,
    21.        sp.value AS spfile_value      
    22. FROM   v$spparameter sp,
    23.        v$parameter p,
    24.        v$instance i
    25. WHERE  sp.name   = p.name
    26. AND    sp.value != p.value;
    27.  
    28. COLUMN FORMAT DEFAULT
    29. [oracle@MaxwellDBA monitoring]$

    47.Script: parameters.sql

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

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

     

    48.Script: parameters_non_default.sql

    -- Description  : Displays a list of all the non-default parameters.

    1. [oracle@MaxwellDBA monitoring]$ cat parameters_non_default.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/parameters_non_default.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays a list of all the non-default parameters.
    6. -- Requirements : Access to the v$ views.
    7. -- Call Syntax  : @parameters_non_default
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. SET LINESIZE 150
    11.  
    12. COLUMN name FORMAT A50
    13. COLUMN value FORMAT A50
    14.  
    15. SELECT name,
    16.        value
    17. FROM   v$parameter
    18. WHERE  isdefault = 'FALSE';
    19. [oracle@MaxwellDBA monitoring]$ 
    1. SQL> clea scre
    2. SQL> @/home/oracle/oracledba/monitoring/parameters_non_default.sql 
    3.  
    4. NAME                                               VALUE
    5. -------------------------------------------------- --------------------------------------------------
    6. processes                                          300
    7. nls_language                                       AMERICAN
    8. nls_territory                                      AMERICA
    9. memory_target                                      1509949440
    10. control_files                                      /opt/oracle/oradata/ORCLCDB/control01.ctl, /opt/or
    11.                                                    acle/oradata/ORCLCDB/control02.ctl
    12.  
    13. db_block_size                                      8192
    14. compatible                                         19.0.0
    15. undo_tablespace                                    UNDOTBS1
    16. remote_login_passwordfile                          EXCLUSIVE
    17.  
    18. NAME                                               VALUE
    19. -------------------------------------------------- --------------------------------------------------
    20. dispatchers                                        (PROTOCOL=TCP) (SERVICE=ORCLCDBXDB)
    21. local_listener                                     LISTENER_ORCLCDB
    22. audit_file_dest                                    /opt/oracle/admin/ORCLCDB/adump
    23. audit_trail                                        DB
    24. db_name                                            ORCLCDB
    25. open_cursors                                       300
    26. diagnostic_dest                                    /opt/oracle
    27. enable_pluggable_database                          TRUE
    28.  
    29. 17 rows selected.
    30.  
    31. SQL>

    49.Script: default_tablespace.sql

    -- Description  : Displays the default temporary and permanent tablespaces.

    1. [oracle@MaxwellDBA monitoring]$ cat default_tablespaces.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/default_tablespaces.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays the default temporary and permanent tablespaces.
    6. -- Requirements : Access to the DATABASE_PROPERTIES views.
    7. -- Call Syntax  : @default_tablespaces
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10. COLUMN property_name FORMAT A30
    11. COLUMN property_value FORMAT A30
    12. COLUMN description FORMAT A50
    13. SET LINESIZE 200
    14.  
    15. SELECT *
    16. FROM   database_properties
    17. WHERE  property_name like '%TABLESPACE';
    18. [oracle@MaxwellDBA monitoring]$
    1. SQL> @/home/oracle/oracledba/monitoring/default_tablespaces.sql 
    2.  
    3. PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
    4. ------------------------------ ------------------------------ --------------------------------------------------
    5. DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace
    6. DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
    7.  
    8. 2 rows selected.
    9.  
    10. SQL>

    50.Script: df_free_space.sql

    -- Description  : Displays free space information about datafiles.

    1. [oracle@MaxwellDBA monitoring]$ cat df_free_space.sql
    2. -- -----------------------------------------------------------------------------------
    3. -- File Name    : /monitoring/df_free_space.sql
    4. -- Author       : Maxwell
    5. -- Description  : Displays free space information about datafiles.
    6. -- Requirements : Access to the V$ views.
    7. -- Call Syntax  : @df_free_space.sql
    8. -- Last Modified: 06-AUG-2022
    9. -- -----------------------------------------------------------------------------------
    10.  
    11. SET LINESIZE 120
    12. COLUMN file_name FORMAT A60
    13.  
    14. SELECT a.file_name,
    15.        ROUND(a.bytes/1024/1024) AS size_mb,
    16.        ROUND(a.maxbytes/1024/1024) AS maxsize_mb,
    17.        ROUND(b.free_bytes/1024/1024) AS free_mb,
    18.        ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb,
    19.        100 - ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used
    20. FROM   (SELECT file_name,
    21.                 file_id,
    22.                 bytes,
    23.                 GREATEST(bytes,maxbytes) AS maxbytes,
    24.                 GREATEST(bytes,maxbytes)-bytes AS growth
    25.          FROM   dba_data_files) a,
    26.         (SELeCT file_id,
    27.               SUM(bytes) AS free_bytes
    28.          FROM dba_free_space
    29.          GROUP BY file_id) b
    30. WHERE  a.file_id = b.file_id
    31. ORDER BY file_name;
    32. [oracle@MaxwellDBA monitoring]$ 
    1.  
    2. SQL> @/home/oracle/oracledba/monitoring/df_free_space.sql 
    3.  
    4. FILE_NAME                                                       SIZE_MB MAXSIZE_MB    FREE_MB  GROWTH_MB   PCT_USED
    5. ------------------------------------------------------------ ---------- ---------- ---------- ---------- ----------
    6. /opt/oracle/oradata/ORCLCDB/sysaux01.dbf                           1280      32768        106      31488          4
    7. /opt/oracle/oradata/ORCLCDB/system01.dbf                            940      32768          7      31828          3
    8. /opt/oracle/oradata/ORCLCDB/undotbs01.dbf                           295      32768        281      32473          0
    9. /opt/oracle/oradata/ORCLCDB/users01.dbf                               5      32768          2      32763          0
    10.  
    11. 4 rows selected.
    12.  
    13. SQL>

  • 相关阅读:
    javascript数据类型
    SCI论文还迟迟动不了笔？2/3区仅1个月25天录用，看看经验之谈
    LeetCode（14）加油站【数组/字符串】【中等】
    Python基于PHP+MySQL的个人网页设计与实现
    python subprocess.cal调用wkhtmltohtml中遇到的问题
    通达信指标编写，16进制颜色对照表，妈妈再也不用担心颜色不够用了！！
    Unity中关于多线程的一些事
    The Seven Tools of Causal Inference with Reflections on Machine Learning 文章解读
    彻底弄懂Vue的作用域插槽
    「学习笔记」AC 自动机
  • 原文地址：https://blog.csdn.net/u011868279/article/details/126188955