请教:查询各个表空间利用率的SQL语句是什么?

Question

select a.TABLESPACE_NAME,b.total_size,a.user_size,(a.user_size/b.total_size*100) percent
from (select sum(bytes) user_size,TABLESPACE_NAME from dba_segments group by TABLESPACE_NAME) a, (select sum(bytes) total_size,TABLESPACE_NAME from dba_data_files group by TABLESPACE_NAME) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME
/SQL> ed
已写入文件 afiedt.buf  1  select a.TABLESPACE_NAME,b.total_size,a.user_size,(a.user_size/b.total_size
*100) percent
  2* from (select sum(bytes) user_size,TABLESPACE_NAME from dba_segments group b
y TABLESPACE_NAME) a, (select sum(bytes) total_size,TABLESPACE_NAME from dba_dat
a_files group by TABLESPACE_NAME) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME
SQL> /TABLESPACE_NAME                TOTAL_SIZE  USER_SIZE    PERCENT
------------------------------ ---------- ---------- ----------
CWMLITE                          20971520    9764864    46.5625
DRSYS                            20971520   10158080    48.4375
EXAMPLE                         156631040  156041216  99.623431
ODM                              20971520    9764864    46.5625
SYSTEM                          419430400  416612352  99.328125
TOOLS                            10485760    6291456         60
UNDOTBS1                        209715200   11255808  5.3671875
XDB                              39976960   39714816 99.3442623已选择8行。

Answer 1

select b.TABLESPACE_NAME,b.total_size,a.user_size,(a.user_size/b.total_size*100) percent
from (select sum(bytes) user_size,TABLESPACE_NAME from dba_segments group by TABLESPACE_NAME) a, (select sum(bytes) total_size,TABLESPACE_NAME from dba_data_files group by TABLESPACE_NAME) b where a.TABLESPACE_NAME(+)=b.TABLESPACE_NAME;
这句更好，不过还是没有TEMP表空间的，TEMP表空间属于另外要讨论的范畴了~