檢視資料庫中tablespace和datafile的使用情況。

by tablespace(使用單位G):

select a.tablespace_name,(all_size-b.unuse_size) use_size,b.unuse_size,a.all_size,

round((a.all_size-b.unuse_size)/a.all_size,2) usage

from (

select tablespace_name,round(sum(bytes)/1024/1024/1024,2) all_size from dba_data_files group by tablespace_name) a,

(select tablespace_name,round(sum(bytes)/1024/1024/1024,2) unuse_size from dba_free_space group by tablespace_name) b

where a.tablespace_name=b.tablespace_name

by datafile(使用單位G):

select a.tablespace_name,a.file_name,(a.all_size-nvl(b.unuse_size,0)) use_size,nvl(b.unuse_size,0),

a.all_size,round((a.all_size-nvl(b.unuse_size,0))/a.all_size,2) usage,a.autoextensible from

(select tablespace_name,file_id,file_name,round(bytes/1024/1024/1024,4) all_size,autoextensible

from dba_data_files) a,

(select file_id,round(sum(bytes)/1024/1024/1024,2) unuse_size from dba_free_space group by file_id)b

where a.file_id=b.file_id(+)

order by a.tablespace_name,file_name

[@more@]