表空间查询

--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
    D.TOT_GROOTTE_MB "表空间大小(M)",
    D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
    TO_CHAR(
        ROUND(
            (D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
            2
        ),
        '990.99'
    ) || '%' "使用比",
    F.TOTAL_BYTES "空闲空间(M)",
    F.MAX_BYTES "最大块(M)"
FROM (
        SELECT TABLESPACE_NAME,
            ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
            ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
        FROM SYS.DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) F,
    (
        SELECT DD.TABLESPACE_NAME,
            ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
        FROM SYS.DBA_DATA_FILES DD
        GROUP BY DD.TABLESPACE_NAME
    ) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC,
    5 ASC;

--查询表空间的free space
SELECT TABLESPACE_NAME,
    COUNT(*) AS EXTENDS,
    ROUND(SUM(BYTES) / 1024 / 1024, 2) AS MB,
    SUM(BLOCKS) AS BLOCKS
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
ORDER BY 3 ASC;

--查询表空间的总容量
SELECT TABLESPACE_NAME,
    SUM(BYTES) / 1024 / 1024 AS MB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
ORDER BY 2 DESC;

--查询表空间使用率
SELECT TOTAL.TABLESPACE_NAME,
    ROUND(TOTAL.MB, 2) AS TOTAL_MB,
    ROUND(TOTAL.MB - FREE.MB, 2) AS USED_MB,
    ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS USED_PCT
FROM (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) / 1024 / 1024 AS MB
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) FREE,
    (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) / 1024 / 1024 AS MB
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
    ) TOTAL
WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
ORDER BY 4 DESC;

SELECT A.TABLESPACE_NAME "表空间名",
    TOTAL "表空间大小",
    FREE "表空间剩余大小",
    (TOTAL - FREE) "表空间使用大小",
    ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) FREE
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) A,
    (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) TOTAL
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
    ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 5 DESC;

SELECT TABLESPACE_NAME "表空间",
    TO_CHAR(ROUND(BYTES / 1024, 2), '99990.00') || '' "实有",
    TO_CHAR(ROUND(FREE / 1024, 2), '99990.00') || 'G' "现有",
    TO_CHAR(ROUND((BYTES - FREE) / 1024, 2), '99990.00') || 'G' "使用",
    TO_CHAR(ROUND(10000 * USED / BYTES) / 100, '99990.00') || '%' "使用比例"
FROM (
        SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
            FLOOR(A.BYTES / (1024 * 1024)) BYTES,
            FLOOR(B.FREE / (1024 * 1024)) FREE,
            FLOOR((A.BYTES - B.FREE) / (1024 * 1024)) USED
        FROM (
                SELECT TABLESPACE_NAME TABLESPACE_NAME,
                    SUM(BYTES) BYTES
                FROM DBA_DATA_FILES
                GROUP BY TABLESPACE_NAME
            ) A,
            (
                SELECT TABLESPACE_NAME TABLESPACE_NAME,
                    SUM(BYTES) FREE
                FROM DBA_FREE_SPACE
                GROUP BY TABLESPACE_NAME
            ) B
        WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
    ) --WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER BY FLOOR(10000 * USED / BYTES) DESC;

SELECT TABLESPACE_NAME,
    MAX_GB,
    USED_GB,
    ROUND(100 * USED_GB / MAX_GB) PCT_USED
FROM (
        SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
            ROUND(
                (A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / POWER(2, 30),
                2
            ) USED_GB,
            ROUND(A.MAXBYTES / POWER(2, 30), 2) MAX_GB
        FROM (
                SELECT F.TABLESPACE_NAME,
                    SUM(F.BYTES) BYTES_ALLOC,
                    SUM(
                        DECODE(
                            F.AUTOEXTENSIBLE,
                            'YES',
                            F.MAXBYTES,
                            'NO',
                            F.BYTES
                        )
                    ) MAXBYTES
                FROM DBA_DATA_FILES F
                GROUP BY TABLESPACE_NAME
            ) A,
            (
                SELECT F.TABLESPACE_NAME,
                    SUM(F.BYTES) BYTES_FREE
                FROM DBA_FREE_SPACE F
                GROUP BY TABLESPACE_NAME
            ) B
        WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
        UNION ALL
        SELECT H.TABLESPACE_NAME TABLESPACE_NAME,
            ROUND(SUM(NVL(P.BYTES_USED, 0)) / POWER(2, 30), 2) USED_GB,
            ROUND(
                SUM(
                    DECODE(
                        F.AUTOEXTENSIBLE,
                        'YES',
                        F.MAXBYTES,
                        'NO',
                        F.BYTES
                    )
                ) / POWER(2, 30),
                2
            ) MAX_GB
        FROM VTEMPSPACEHEADERH,
            VTEMP_EXTENT_POOL P,
            DBA_TEMP_FILES F
        WHERE P.FILE_ID(+) = H.FILE_ID
            AND P.TABLESPACE_NAME(+) = H.TABLESPACE_NAME
            AND F.FILE_ID = H.FILE_ID
            AND F.TABLESPACE_NAME = H.TABLESPACE_NAME
        GROUP BY H.TABLESPACE_NAME
    )
ORDER BY 4;

--带获取数据文件及路径
SELECT B.FILE_NAME 物理文件名,
    B.TABLESPACE_NAME 表空间名称,
    B.BYTES / 1024 / 1024 总大小M,
    (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
    SUBSTR(
        (B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100,
        1,
        5
    ) 使用率
FROM DBA_FREE_SPACE A,
    DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID
GROUP BY B.TABLESPACE_NAME,
    B.FILE_NAME,
    B.BYTES
ORDER BY B.TABLESPACE_NAME;

SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
    TOTAL / 1048576 TOTAL_M,
    FREE / 1048576 FREE_M,
    (TOTAL - FREE) / 1048576 USED_M,
    ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "USED%",
    AUTOEXTENSIBLE AUTOEXTEM
FROM (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) FREE
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) A,
    (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) TOTAL,
            MAX(AUTOEXTENSIBLE) AUTOEXTENSIBLE
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
    ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 6,
    5 DESC;

--查看表空间的名字及文件所在位置
SELECT TABLESPACE_NAME,
    FILE_ID,
    FILE_NAME,
    ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

--查询表空间使用情况
SELECT A.TABLESPACE_NAME AS "表空间名",
    A.BYTES / 1024 / 1024 AS "表空间大小(M)",
    (A.BYTES - B.BYTES) / 1024 / 1024 AS "已使用空间(M)",
    B.BYTES / 1024 / 1024 "空闲空间(M)",
    ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) "使用比"
FROM (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) BYTES
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
    ) A,
    (
        SELECT TABLESPACE_NAME,
            SUM(BYTES) BYTES,
            MAX(BYTES) LARGEST
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) DESC;

SELECT A.A1 表空间名称,
    C.C2 类型,
    C.C3 区管理,
    B.B2 / 1024 / 1024 表空间大小M,
    (B.B2 - A.A2) / 1024 / 1024 已使用M,
    SUBSTR((B.B2 - A.A2) / B.B2 * 100, 1, 5) 利用率
FROM (
        SELECT TABLESPACE_NAME A1,
            SUM(NVL(BYTES, 0)) A2
        FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME
    ) A,
    (
        SELECT TABLESPACE_NAME B1,
            SUM(BYTES) B2
        FROM DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
    ) B,
    (
        SELECT TABLESPACE_NAME C1,
            CONTENTS C2,
            EXTENT_MANAGEMENT C3
        FROM DBA_TABLESPACES
    ) C
WHERE A.A1 = B.B1
    AND C.C1 = B.B1
ORDER BY 6 DESC;

---所有用户表使用大小的前三十名
SELECT *
FROM (
        SELECT SEGMENT_NAME,
            BYTES
        FROM DBA_SEGMENTS
        WHERE OWNER = USER
        ORDER BY BYTES DESC
    )
WHERE ROWNUM <= 30;
--有两种含义的表大小。一种是分配给一个表的物理空间数量，而不管空间是否被使用。可以这样查询获得字节数：
SELECT SEGMENT_NAME,
    BYTES
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE';
--or
SELECT SEGMENT_NAME,
    SUM(BYTES) / 1024 / 1024
FROM USER_EXTENTS
GROUP BY SEGMENT_NAME;
--另一种表实际使用的空间
ANALYZE TABLE EMP COMPUTE STATISTICS;
SELECT NUM_ROWS * AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME = 'T_TEST';

SELECT NUM_ROWS * AVG_ROW_LEN,
    T.*
FROM DBA_TABLES T
WHERE TABLE_NAME = 'T_TEST';

参考链接

• https://www.cnblogs.com/ritchy/p/10917393.html

• https://www.cnblogs.com/xwdreamer/p/3511047.html