Tablespaces

Here are some scripts, tips regarding the tablespaces. This script basically gives you all the tablespace info in a database.

****************** BEGIN -- Tablespace info script ******************

set linesize 150;

select ddf.TABLESPACE_NAME Tablespace,
ddf.BYTES/1024/1024 MBytes_allocated,
(ddf.BYTES-DFS.BYTES)/1024/1024 MBytes_used,
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) Percent_used,
dfs.BYTES/1024/1024 MBytes_free,
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) Percent_free
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc;

****************** END-- Tablespace info script *****************