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    ) dbfINNER JOIN    (SELECT        tt.tablespace_name,        SUM(tt.bytes) AS freespace    FROM        dba_free_space tt    GROUP BY        tt.tablespace_name    ) dfsON    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_spaceFROM    (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    ) TotalWHERE    Free.Tablespace_name = Total.name;