查询Oracle数据库段SEGMENT和对象大小

来源：互联网发布：java unicode转换编辑：程序博客网时间：2024/05/16 06:54

一、查询段类别：
SELECT T.TABLESPACE_NAME, SEGMENT_TYPE, COUNT(1)
FROM USER_SEGMENTS T
WHERE 1 = 1
GROUP BY T.TABLESPACE_NAME, T.SEGMENT_TYPE
ORDER BY T.TABLESPACE_NAME, T.SEGMENT_TYPE;

二、查看数据库表空间存储对象的大小
SELECT *
FROM (SELECT SEGMENT_NAME,
               T.SEGMENT_TYPE,
               SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE
          FROM USER_SEGMENTS T
         WHERE 1 = 1
         GROUP BY T.SEGMENT_NAME, T.SEGMENT_TYPE) T
WHERE 1 = 1
ORDER BY SEGMENT_SIZE DESC;

三、查看表空间占的总大小：
SELECT SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE FROM USER_SEGMENTS T;

四、实际表占用空间大小，包括表所属对象：INDEX、LOBINDEX、LOBSEGMENT
SELECT *
FROM (SELECT SEGMENT_NAME,
               SEGMENT_TYPE,
               SUM(BYTES) / 1024 / 1024 SEGMENT_SIZE
          FROM (SELECT T.TABLESPACE_NAME,
                       T.SEGMENT_NAME,
                       T.SEGMENT_TYPE,
                       T.BYTES
                  FROM USER_SEGMENTS T
                 WHERE 1 = 1
                   AND T.SEGMENT_TYPE NOT IN
                       ('INDEX', 'LOBINDEX', 'LOBSEGMENT')
                UNION ALL
                SELECT T.TABLESPACE_NAME,
                       UL.TABLE_NAME AS SEGMENT_NAME,
                       'TABLE' AS SEGMENT_TYPE,
                       T.BYTES
                  FROM USER_SEGMENTS T
                 INNER JOIN USER_LOBS UL
                    ON T.SEGMENT_NAME = UL.SEGMENT_NAME
                 WHERE 1 = 1
                   AND T.SEGMENT_TYPE = 'LOBSEGMENT'
                UNION ALL
                SELECT T.TABLESPACE_NAME,
                       UI.TABLE_NAME AS SEGMENT_NAME,
                       'TABLE' AS SEGMENT_TYPE,
                       T.BYTES
                  FROM USER_SEGMENTS T
                 INNER JOIN USER_INDEXES UI
                    ON T.SEGMENT_NAME = UI.INDEX_NAME
                 WHERE 1 = 1
                   AND T.SEGMENT_TYPE IN ('INDEX', 'LOBINDEX')
                   AND 1 = 1) T
         WHERE 1 = 1
         GROUP BY SEGMENT_NAME, SEGMENT_TYPE) T
WHERE 1 = 1
ORDER BY SEGMENT_SIZE DESC;

0 0