27th Mar, 2006, 7:54am
Hows my tablespaces?
select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Produces something like this:
| TABLESPACE_NAME | BYTES_USED | BYTES_FREE | LARGEST | PERCENT_USED |
| —————————— | ———- | ———- | ———- | ———— |
| SYSTEM | 241172480 | 10289152 | 9371648 | 95.73 |
| SYSAUX | 157286400 | 11206656 | 7274496 | 92.88 |
| USERS | 220200960 | 22609920 | 17367040 | 89.73 |
| MTD_DATA | 104857600 | 25624576 | 19398656 | 75.56 |
| UNDO | 10485760 | 6750208 | 3145728 | 35.63 |
| SQL> |
from http://vsbabu.org/oracle/sect03.html
1 Comment »
Matt on 21 Jul 2006 at 4:39pm #
Modified it to provide sizes in MB, and round off.
select a.TABLESPACE_NAME,
round(a.BYTES/(1024*1024),1) MB_used,
round(b.BYTES/(1024*1024),1) MB_free,
round(b.largest/(1024*1024),1) largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,1) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;