Oracle 日常健康脚本

发布时间:2024年01月24日

文章目录

摘要

保持 Oracle 数据库的良好健康状况对于系统的可靠性和性能至关重要。本文将介绍一些常用的 Oracle 日常健康脚本,帮助您监控数据库并及时识别潜在的问题,以保证数据库的稳定运行。

常用脚本

1.查询数据库实例和实例级别的信息
通过 gv d a t a b a s e 、 g v database、 gv databasegvinstance
查询数据库实例和实例级别的信息。
获取数据库名称、实例名称、平台名称、主机名称、数据库版本和启动时间等信息。

SELECT a.NAME DBNAME,
       b.instance_name,
       a.PLATFORM_NAME,
       b.host_name,
       b.version,
       b.startup_time
  FROM gV$DATABASE a, gv$instance b
where a.inst_id = b.inst_id
order by 2;

2.查询数据库注册表历史记录的详细信息
包括操作时间、动作和注释。通过按照时间降序排序,可以查看最近执行的注册表更改操作和对应的注释。

SELECT TO_CHAR(ACTION_TIME, 'YYYY-MM-DD HH24:MI:SS')  ACTION_TIME,
       ACTION,
       COMMENTS
  FROM SYS.DBA_REGISTRY_HISTORY
ORDER BY 1 DESC;

3.查询从V$LOG_HISTORY表中检索最近10天的日志记录
按照日期和小时统计每个小时的日志数量。查询结果按日期和小时排序。
查询使用了SUBSTR函数来提取日期和小时部分,并通过TO_CHAR函数格式化为’MM/DD/RR HH24:MI:SS’的形式。然后使用DECODE函数根据小时值进行条件判断,如果符合条件则返回1,否则返回0。使用SUM函数对每个小时的返回值进行求和,得到每个小时的日志数量。最后使用COUNT函数统计总日志数量。

SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'), 1,  5) DAY,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '00',
                  1,
                  0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '01',
                  1,
                  0)) H01,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '02',
                  1,
                  0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '03',
                  1,
                  0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '04',
                  1,
                  0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '05',
                  1,
                  0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '06',
                  1,
                  0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '07',
                  1,
                  0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '08',
                  1,
                  0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '09',
                  1,
                  0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '10',
                  1,
                  0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '11',
                  1,
                  0)) H11,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '12',
                  1,
                  0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '13',
                  1,
                  0)) H13,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '14',
                  1,
                  0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '15',
                  1,
                  0)) H15,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '16',
                  1,
                  0)) H16,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '17',
                  1,
                  0)) H17,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '18',
                  1,
                  0)) H18,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '19',
                  1,
                  0)) H19,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '20',
                  1,
                  0)) H20,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '21',
                  1,
                  0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '22',
                  1,
                  0)) H22,
       SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR  HH24:MI:SS'), 10, 2),
                  '23',
                  1,
                  0)) H23,
       COUNT(*) TOTAL
  FROM V$LOG_HISTORY A
WHERE FIRST_TIME > TRUNC(SYSDATE - 10)
GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),  1, 5)
ORDER BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),  1, 5);

4.检索有关数据库存储和结构的统计信息
TOTAL STORAGES 和对应的总存储大小
INDEX STORAGES 和对应的索引存储大小
TABLESPACES 和对应的表空间数量
FILES 和对应的数据文件数量
TOTAL TABLES 和对应的表数量
TOTAL INDEXES 和对应的索引数量
DB_BLOCK_SIZE 和对应的数据块大小
NLS_CHARACTERSET 和对应的字符集
NLS_NCHAR_CHARACTERSET 和对应的NCHAR字符集

set linesize 100
set pages 200
col ITEM  format a30
col TOTAL_SIZE  format a30
SELECT 'TOTAL STORAGES' AS ITEM,
       ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2) || 'GB'  TOTAL_SIZE
  FROM DBA_SEGMENTS
UNION ALL
SELECT 'INDEX STORAGES', ROUND(SUM(BYTES) / 1024 / 1024 /  1024, 2) || 'GB'
  FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
UNION ALL
SELECT 'TABLESPACES', TO_CHAR(COUNT(*))
  FROM V$TABLESPACE
UNION ALL
SELECT 'FILES', TO_CHAR(COUNT(*))
  FROM V$DATAFILE
UNION ALL
SELECT 'TOTAL TABLES', TO_CHAR(COUNT(*))
  FROM DBA_TABLES
UNION ALL
SELECT 'TOTAL INDEXES', TO_CHAR(COUNT(*))
  FROM DBA_INDEXES
UNION ALL
SELECT 'DB_BLOCK_SIZE', VALUE
  FROM V$PARAMETER
WHERE TRIM(UPPER(NAME)) = 'DB_BLOCK_SIZE'
UNION ALL
SELECT T1.PARAMETER, T1.VALUE
  FROM V$NLS_PARAMETERS T1
WHERE UPPER(TRIM(T1.PARAMETER)) IN
       ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

5.检索ASM磁盘的相关信息
从V$ASM_DISK视图检索ASM磁盘的相关信息
GROUP_NUMBER:ASM磁盘所属的磁盘组编号。
DISK_NUMBER:ASM磁盘的编号。
TOTAL_MB:ASM磁盘的总容量(以MB为单位)。
FREE_MB:ASM磁盘的可用容量(以MB为单位)。
NAME:ASM磁盘的名称。
FAILGROUP:ASM磁盘所属的失效组。
PATH:ASM磁盘的路径。
CREATE_DATE:ASM磁盘的创建日期和时间。

set linesize 400
set pages 300
col NAME  format a15
col FAILGROUP  format a15
col PATH  format a30
SELECT GROUP_NUMBER,
       DISK_NUMBER,
       TOTAL_MB,
       FREE_MB,
       NAME,
       FAILGROUP,
       PATH,
       TO_CHAR(CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')  CREATE_DATE
  FROM V$ASM_DISK A
ORDER BY 1;

6.检索ASM磁盘组的相关信息
GROUP_NUMBER:ASM磁盘组的编号。
NAME:ASM磁盘组的名称。
STATE:ASM磁盘组的状态。可能的值包括:MOUNTED(已挂载)、DISMOUNTED(已卸载)、UNKNOWN(未知)等。
TYPE:ASM磁盘组的类型。例如,NORMAL(普通磁盘组)或HIGH_REDUNDANCY(高冗余磁盘组)。
TOTAL_MB:ASM磁盘组的总容量(以MB为单位)。
FREE_MB:ASM磁盘组的可用容量(以MB为单位)。

这条SQL查询用于从V$ASM_DISKGROUP视图检索ASM磁盘组的相关信息,并按照GROUP_NUMBER和NAME进行排序。

SELECT A.GROUP_NUMBER,A.NAME,A.STATE,A.TYPE, A.TOTAL_MB, A.FREE_MB     FROM V$ASM_DISKGROUP A  ORDER BY 1, 2

7.查看表空间的相关信息
STATUS:表空间的状态。
NAME:表空间的名称。
TYPE:表空间的类型。
EXTENT_MGT:表空间的数据段管理方式。
SEGMENT_MGT:表空间的段空间管理方式。
TS_SIZE:表空间的总大小(以MB为单位)。
FREE:表空间的可用空间(以MB为单位)。
USED:表空间的已使用空间(以MB为单位)。
PCT_USED:表空间的使用率。

第一部分查询从SYS.DBA_TABLESPACES表、DBA_DATA_FILES表和DBA_FREE_SPACE表中检索非临时表空间的相关信息,并计算出表空间的总大小、可用空间、已使用空间以及使用率。
第二部分查询从SYS.DBA_TABLESPACES表、DBA_TEMP_FILES表和V$TEMP_EXTENT_POOL表中检索临时表空间的相关信息,并计算出表空间的总大小、可用空间、已使用空间以及使用率。

SELECT STATUS,
       NAME,
       TYPE,
       EXTENT_MGT,
       SEGMENT_MGT,
       TS_SIZE,
       FREE,
       USED,
       PCT_USED
  FROM (SELECT DECODE(D.STATUS, 'OFFLINE', D.STATUS,  D.STATUS) STATUS,
               D.TABLESPACE_NAME NAME,
               D.CONTENTS TYPE,
               D.EXTENT_MANAGEMENT EXTENT_MGT,
               D.SEGMENT_SPACE_MANAGEMENT SEGMENT_MGT,
               ROUND(NVL(A.BYTES, 0) / 1024 / 1024, 0)  TS_SIZE,
               ROUND(NVL(F.BYTES, 0) / 1024 / 1024, 0) FREE,
               ROUND(NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024  / 1024, 0) USED,
               DECODE((1 -
                      SIGN(1 - SIGN(TRUNC(NVL((A.BYTES -  NVL(F.BYTES, 0)) /
                                               A.BYTES * 100,
                                               0)) - 90))),
                      1,
                      TO_CHAR(TRUNC(NVL((A.BYTES -  NVL(F.BYTES, 0)) / A.BYTES * 100,
                                        0))),
                      TO_CHAR(TRUNC(NVL((A.BYTES -  NVL(F.BYTES, 0)) / A.BYTES * 100,
                                        0)))) || '%%' PCT_USED
          FROM SYS.DBA_TABLESPACES D,
               (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) F
         WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
           AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
           AND NOT (D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND
                D.CONTENTS LIKE 'TEMPORARY')
         ORDER BY 2)
UNION ALL
SELECT DECODE(D.STATUS, 'OFFLINE', D.STATUS, D.STATUS)  STATUS,
       D.TABLESPACE_NAME,
       D.CONTENTS TYPE,
       D.EXTENT_MANAGEMENT EXTENT_MGT,
       D.SEGMENT_SPACE_MANAGEMENT SEGMENT_MGT,
       ROUND(NVL(A.BYTES, 0) / 1024 / 1024, 2) TS_SIZE,
       ROUND(NVL(A.BYTES - NVL(T.BYTES, 0), 0) / 1024 /  1024, 2) FREE,
       ROUND(NVL(T.BYTES, 0) / 1024 / 1024, 2) USED,
       DECODE((1 -
              SIGN(1 - SIGN(TRUNC(NVL(T.BYTES / A.BYTES *  100,  0)) - 90))),
              1,
              TO_CHAR(TRUNC(NVL(T.BYTES / A.BYTES * 100,  0))),
              TO_CHAR(TRUNC(NVL(T.BYTES / A.BYTES * 100,  0)))) PCT_USED
  FROM SYS.DBA_TABLESPACES D,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) BYTES
          FROM V$TEMP_EXTENT_POOL
         GROUP BY TABLESPACE_NAME) T
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
   AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
   AND D.EXTENT_MANAGEMENT LIKE 'LOCAL'
   AND D.CONTENTS LIKE 'TEMPORARY'
ORDER BY 2;

8.查询RMAN备份作业的相关信息

包括备份名称(BACKUP_NAME)
开始时间(START_TIME)
耗时(ELAPSED_TIME)
状态(STATUS)
输入类型(INPUT_TYPE)
输出设备类型(OUTPUT_DEVICE_TYPE)
输入大小(INPUT_SIZE)
输出大小(OUTPUT_SIZE)
输出速率(OUTPUT_RATE_PER_SEC)

SELECT R.COMMAND_ID BACKUP_NAME,
       TO_CHAR(R.START_TIME, 'MM/DD/YYYY HH24:MI:SS')  START_TIME,
       R.TIME_TAKEN_DISPLAY ELAPSED_TIME,
       DECODE(R.STATUS,
              'COMPLETED',
              R.STATUS,
              'RUNNING',
              R.STATUS,
              'FAILED',
              R.STATUS,
              R.STATUS) STATUS,
       R.INPUT_TYPE,
       R.OUTPUT_DEVICE_TYPE,
       R.INPUT_BYTES_DISPLAY INPUT_SIZE,
       R.OUTPUT_BYTES_DISPLAY OUTPUT_SIZE,
       R.OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_RATE_PER_SEC
  FROM (SELECT COMMAND_ID,
               START_TIME,
               TIME_TAKEN_DISPLAY,
               STATUS,
               INPUT_TYPE,
               OUTPUT_DEVICE_TYPE,
               INPUT_BYTES_DISPLAY,
               OUTPUT_BYTES_DISPLAY,
               OUTPUT_BYTES_PER_SEC_DISPLAY
          FROM V$RMAN_BACKUP_JOB_DETAILS
         ORDER BY START_TIME DESC) R
WHERE ROWNUM < 30;

9.查看备份集信息(控制文件)

从V$BACKUP_SET,V$BACKUP_PIECE视图查询

BS_KEY:备份集标识(BS.RECID)
PIECE:备份片号(BP.PIECE#)
COPY: 备份片拷贝号(BP.COPY#)
BP_KEY:备份片标识(BP.RECID)
CONTROLFILE_INCLUDED:控制文件是否包含在备份集中的标志列,如果是’NO’则显示为’-',否则显示原值。
STATUS:备份片的状态,将’A’转换为’AVAILABL’,‘D’转换为’DELETED’,‘X’转换为’EXPIRED’。AVAILAB代表可用状态
HANDLE:备份片的句柄信息。格式化为最大长度为25的字符串。
该查询筛选出满足以下条件的备份集和备份片:

备份片的状态为’A’或’X’。
备份集中包含控制文件。
备份集的完成时间在当前时间的前一天以后。

set linesize 500
set pages 500
column BS_KEY format 999999
column PIECE  format 99
column COPY  format 99
column BS_KEY format 999999
column CONTROLFILE_INCLUDED  format a15
column STATUS  format a20
column HANDLE  format a25
SELECT BS.RECID BS_KEY,
       BP.PIECE# PIECE,
       BP.COPY# COPY,
       BP.RECID BP_KEY,
       DECODE(BS.CONTROLFILE_INCLUDED, 'NO', '-', BS.CONTROLFILE_INCLUDED) CONTROLFILE_INCLUDED,
       DECODE(STATUS, 'A', 'AVAILABL', 'D', 'DELETED', 'X', 'EXPIRED') STATUS,
       HANDLE HANDLE
  FROM V$BACKUP_SET BS, V$BACKUP_PIECE BP
WHERE BS.SET_STAMP = BP.SET_STAMP
   AND BS.SET_COUNT = BP.SET_COUNT
   AND BP.STATUS IN ('A', 'X')
   AND BS.CONTROLFILE_INCLUDED != 'NO'
   AND BS.COMPLETION_TIME > SYSDATE - 1
ORDER BY BS.RECID DESC, PIECE# ;

10.获取数据库日志的相关信息
LOG_MODE:数据库的日志模式,将根据LOG_MODE的值进行解码,并显示为更易读的形式。如果LOG_MODE是’ARCHIVELOG’,则显示为’ARCHIVE MODE;如果LOG_MODE是’NOARCHIVELOG’,则显示为’NO ARCHIVE MODE’;否则,将显示原始的LOG_MODE值。
LOG_ARCHIVE_START:日志归档是否启用的标志,根据LOG_MODE进行解码。如果LOG_MODE是’ARCHIVELOG’,则显示为’ENABLED’;如果LOG_MODE是’NOARCHIVELOG’,则显示为’DISABLED’。
CURRENT_LOG_SEQ:当前日志的序列号,从VKaTeX parse error: Expected 'EOF', got '#' at position 35: …’的日志条目的SEQUENCE#?进行获取。 OLDEST_ON…LOG视图中选择SEQUENCE#的最小值作为最旧的在线日志的序列号。

通过执行这个查询,可以获取数据库的日志模式、日志归档是否启用、当前日志的序列号以及最旧的在线日志的序列号。这些信息对于管理和监控数据库日志非常有用。

SELECT D.LOG_MODE,
       P.LOG_ARCHIVE_START,
       C.CURRENT_LOG_SEQ,
       O.OLDEST_ONLINE_LOG_SEQUENCE
  FROM (SELECT DECODE(LOG_MODE,
                      'ARCHIVELOG',
                      'ARCHIVE MODE',
                      'NOARCHIVELOG',
                      'NO ARCHIVE MODE',
                      LOG_MODE) LOG_MODE
          FROM V$DATABASE) D,
       (SELECT DECODE(LOG_MODE,
                      'ARCHIVELOG',
                      'ENABLED',
                      'NOARCHIVELOG',
                      'DISABLED') LOG_ARCHIVE_START
          FROM V$DATABASE) P,
       (SELECT A.SEQUENCE# CURRENT_LOG_SEQ
          FROM V$LOG A
         WHERE A.STATUS = 'CURRENT') C,
       (SELECT MIN(A.SEQUENCE#) OLDEST_ONLINE_LOG_SEQUENCE FROM V$LOG A) O;

11.查询数据库dblink

包含以下列:

OWNER:链接所属的所有者。
DB_LINK:链接的名称。
USERNAME:连接到链接所使用的用户名。
HOST:链接的目标主机。
CREATED:链接的创建时间,格式为 ‘MM/DD/YYYY HH24:MI:SS’
set linesize 500set pagesize 400column OWNER format a10column DB_LINK format a25column USERNAME format a15column HOST format a20column CREATED format a20SELECT OWNER , DB_LINK , USERNAME , HOST , TO_CHAR(CREATED, 'MM/DD/YYYY HH24:MI:SS') CREATED FROM DBA_DB_LINKS ORDER BY OWNER, DB_LINK;

12.查询表的高水位线

SELECT *  FROM (SELECT OWNER,               SEGMENT_NAME TABLE_NAME,               SEGMENT_TYPE,               NVL(HWM - AVG_USED_BLOCKS, 0) WASTE_BLOCKS,               GREATEST(ROUND(100 * (NVL(HWM -  AVG_USED_BLOCKS, 0) /                              GREATEST(NVL(HWM, 1), 1)),                              2),                        0) WASTE_PER,               ROUND(BYTES / 1024 / 1024, 2) TABLE_MB,               ROUND(BYTES / 1024 / 1024, 2) *               GREATEST(ROUND((NVL(HWM - AVG_USED_BLOCKS, 0)  /                              GREATEST(NVL(HWM, 1), 1)),                              2),                        0) WASTE_MB,               O_TABLESPACE_NAME TABLESPACE_NAME          FROM (SELECT A.OWNER OWNER,                       A.SEGMENT_NAME,                       A.SEGMENT_TYPE,                       A.BYTES,                       B.NUM_ROWS,                       A.BLOCKS BLOCKS,                       B.EMPTY_BLOCKS EMPTY_BLOCKS,                       A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,                       DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS  *                                    (1 + (PCT_FREE / 100))) /  C.BLOCKSIZE,                                    0),                              0,                              1,                              ROUND((B.AVG_ROW_LEN * NUM_ROWS  *                                    (1 + (PCT_FREE / 100))) /  C.BLOCKSIZE,                                    0)) + 2 AVG_USED_BLOCKS,                       ROUND(100 * (NVL(B.CHAIN_CNT, 0) /                             GREATEST(NVL(B.NUM_ROWS, 1), 1)),                             2) CHAIN_PER,                       ROUND(100 * (A.EXTENTS /  A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,                       A.EXTENTS EXTENTS,                       A.MAX_EXTENTS MAX_EXTENTS,                       B.NEXT_EXTENT NEXT_EXTENT,                       B.TABLESPACE_NAME O_TABLESPACE_NAME                  FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B,  SYS.TS$ C                 WHERE A.OWNER = B.OWNER                   AND SEGMENT_NAME = TABLE_NAME                   AND SEGMENT_TYPE = 'TABLE'                   AND B.TABLESPACE_NAME = C.NAME                UNION ALL                SELECT A.OWNER OWNER,                       SEGMENT_NAME || '.' ||  B.PARTITION_NAME,                       SEGMENT_TYPE,                       BYTES,                       B.NUM_ROWS,                       A.BLOCKS BLOCKS,                       B.EMPTY_BLOCKS EMPTY_BLOCKS,                       A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,                       DECODE(ROUND((B.AVG_ROW_LEN *  B.NUM_ROWS *                                    (1 + (B.PCT_FREE / 100))) /  C.BLOCKSIZE,                                    0),                              0,                              1,                              ROUND((B.AVG_ROW_LEN *  B.NUM_ROWS *                                    (1 + (B.PCT_FREE / 100))) /  C.BLOCKSIZE,                                    0)) + 2 AVG_USED_BLOCKS,                       ROUND(100 * (NVL(B.CHAIN_CNT, 0) /                             GREATEST(NVL(B.NUM_ROWS, 1), 1)),                             2) CHAIN_PER,                       ROUND(100 * (A.EXTENTS /  A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,                       A.EXTENTS EXTENTS,                       A.MAX_EXTENTS MAX_EXTENTS,                       B.NEXT_EXTENT,                       B.TABLESPACE_NAME O_TABLESPACE_NAME                  FROM SYS.DBA_SEGMENTS       A,                       SYS.DBA_TAB_PARTITIONS B,                       SYS.TS$                C,                       SYS.DBA_TABLES         D                 WHERE A.OWNER = B.TABLE_OWNER                   AND SEGMENT_NAME = B.TABLE_NAME                   AND SEGMENT_TYPE = 'TABLE PARTITION'                   AND B.TABLESPACE_NAME = C.NAME                   AND D.OWNER = B.TABLE_OWNER                   AND D.TABLE_NAME = B.TABLE_NAME                   AND A.PARTITION_NAME = B.PARTITION_NAME),               (SELECT TABLESPACE_NAME F_TABLESPACE_NAME,                       MAX(BYTES) MAX_FREE_SPACE                  FROM SYS.DBA_FREE_SPACE                 GROUP BY TABLESPACE_NAME)         WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME           AND GREATEST(ROUND(100 * (NVL(HWM -  AVG_USED_BLOCKS, 0) /                              GREATEST(NVL(HWM, 1), 1)),                              2),                        0) > 20           AND OWNER NOT IN ('CTXSYS',                             'DBSNMP',                             'DMSYS',                             'EXFSYS',                             'IX',                             'LBACSYS',                             'MDSYS',                             'OLAPSYS',                             'ORDSYS',                             'OUTLN',                             'SYS',                             'SYSMAN',                             'SYSTEM',                             'WKSYS',                             'WMSYS',                             'XDB',                             'APEX_030200',                             'ORDDATA')           AND BLOCKS > 128         ORDER BY 4 DESC, 1 ASC, 2 ASC)where rownum <= 30;
文章来源:https://blog.csdn.net/m0_49929446/article/details/135821022
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。