/*表空间查询*/

SELECT d.status "状态",

            d.tablespace_name "名称",

            d.contents "类型",

            TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "大小 (M)",

            TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "已使用 (M)",

            TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "占用率 %"

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(+)  

ORDER BY D.TABLESPACE_NAME;  

-------------------------------------------------------------------------------------------------

/*表空间查询*/

select a.file_id FileNo,

       a.tablespace_name        "Tablespace_name",

       a.bytes "Bytes",

       a.bytes - sum(nvl(b.bytes, 0)) Used,

       sum(nvl(b.bytes, 0)) Free,

       sum(nvl(b.bytes, 0)) / a.bytes * 100 "%free" -- 空间可用

from dba_data_files a,  

      dba_free_space b

where a.file_id = b.file_id(+)  

group by a.tablespace_name,

          a.file_id,

          a.bytes  order by a.tablespace_name;