Prompt Tablespace Usage:

set linesize 180

select * from

(select

d.tablespace_name,space1 "Total", space "sum_space(M)",

space-nvl(free_space,1) "used_space(M)",

space1-(space-nvl(free_space,0)) "free_space(M)",

round((space-nvl(free_space,1))/space1*100,2) "used_rate(%)"

from

(select

tablespace_name,

round(sum(decode(maxbytes,0,bytes,maxbytes))/(1024*1024),2) space1,

round(sum(bytes)/(1024*1024),2) space

from dba_data_files

group by tablespace_name) d,

(select

tablespace_name,

round(sum(bytes)/(1024*1024),2) free_space

from dba_free_space

group by tablespace_name) f

where d.tablespace_name=f.tablespace_name(+)

order by round((space-nvl(free_space,1))/space1*100,2) desc ) where rownum<10

/

Prompt TEMP Tablespace Usage:

select

d.tablespace_name,space1 "Total", space "sum_space(M)",

space-nvl(free_space,1) "used_space(M)",

space1-(space-nvl(free_space,0)) "free_space(M)",

round((space-nvl(free_space,1))/space1*100,2) "used_rate(%)"

from

(select

tablespace_name,

round(sum(decode(maxbytes,0,bytes,maxbytes))/(1024*1024),2) space1,

round(sum(bytes)/(1024*1024),2) space

from dba_temp_files

group by tablespace_name) d,

(select

tablespace_name,

round(sum(bytes)/(1024*1024),2) free_space

from dba_free_space

group by tablespace_name) f

where d.tablespace_name=f.tablespace_name(+)

order by round((space-nvl(free_space,1))/space1*100,2) desc

/

Prompt Tablespace Total/Used/Free Size:

select a.GB "Total GB",(a.GB-b.GB) "Used GB",b.GB "Free GB" from

(select (sum(bytes)/1024/1024/1024) GB from dba_data_files) a,

(select (sum(bytes)/1024/1024/1024) GB from dba_free_space) b

/

exit;

ITPub博客

Get Tablespace Real usage

最新文章