A simple script to see permanent / temporary tablespace usage in Oracle
=================================================
set linesize 250
set pagesize 200
set echo off
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2) as "%_USED_SPACE"
from
(select sum(bytes)/1024/1024 free_space_in_mb, tablespace_name from dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_data_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc
;
select b.tablespace_name,b.total_space_in_mb,(b.total_space_in_mb - nvl(a.free_space_in_mb,0)) used_space_in_mb,
nvl(a.free_space_in_mb,0) free_space_MB, round(((b.total_space_in_mb - nvl(a.free_space_in_mb,0)) / b.total_space_in_mb) * 100,2)
as "%_USED_SPACE" from
(select sum(bytes_free)/1024/1024 free_space_in_mb, tablespace_name from v$temp_space_header group by tablespace_name) a,
(select sum(bytes)/1024/1024 total_space_in_mb,tablespace_name from dba_temp_files group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name order by "%_USED_SPACE" desc ;