Oracle之 查看所有表空间的大小

表空间的查询主要是以下四个数据字典：

select * from dba_data_files t;
select * from dba_free_space t;
select * from dba_temp_files t;
select * from dba_temp_free_space t;

查询方法一：

SELECT
    dbf.tablespace_name AS "Tablespace Name",
    (dbf.totalspace / 1024 / 1024) AS "Total Space(M)",
    (dfs.freespace / 1024 / 1024) AS "Free Space(M)",
    ROUND((dfs.freespace / dbf.totalspace) * 100, 2) AS "Free Ratio(%)",
    ROUND((dbf.maxspace / 1024 / 1024)) AS "Max Space(M)",
    ROUND((dbf.userspace / 1024 / 1024)) AS "User Space(M)",
    (vp.value / 1024) AS "Block Value(K)"
FROM
    v$parameter vp,
    (SELECT
        t.tablespace_name,
        SUM(t.bytes) AS totalspace,
        SUM(t.maxbytes) AS maxspace,
        SUM(t.user_bytes) AS userspace
    FROM
        dba_data_files t
    GROUP BY
        t.tablespace_name
    ) dbf
INNER JOIN
    (SELECT
        tt.tablespace_name,
        SUM(tt.bytes) AS freespace
    FROM
        dba_free_space tt
    GROUP BY
        tt.tablespace_name
    ) dfs
ON
    trim(dbf.tablespace_name) = trim(dfs.tablespace_name),

where vp.name='db_block_size'
order by "Tablespace Name";

查询方法二：

SELECT
    Total.name as "Tablespace Name",
    Free_space,
    (total_space-Free_space) Used_space,
    total_space
FROM
    (SELECT
        tablespace_name,
        SUM(bytes/1024/1024) Free_Space
    FROM
        sys.dba_free_space
    GROUP BY
        tablespace_name
    ) Free,
    (SELECT
        b.name,
        SUM(bytes/1024/1024) TOTAL_SPACE
    FROM
        sys.v_$datafile a,
        sys.v_$tablespace B
    WHERE
        a.ts# = b.ts#
    GROUP BY
        b.name
    ) Total
WHERE
    Free.Tablespace_name = Total.name;