How to monitor tablespace usage in Oracle

You need a user with access to the following tables

    dba_free_space
    dba_data_files
    dba_tablespaces

If you get your DBA to create this user for you, here's the SQL...

SQL> create user monitor identified by BPM123bpm;
SQL> grant create session, select any table, select any dictionary to monitor;

Now you can run queries like this...
- To show the size of each tablespace...

select tablespace_name,sum(bytes) from dba_data_files group by tablespace_name;

- To show the actual usage of each tablespace...

select tablespace_name,sum(bytes) from dba_segments group by tablespace_name;

- Or a nice script that somebody gave me is below...

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
/
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_temp_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;