1. Access to Database
CMD > sqlplus /nolog
SQL > connect sys@protect as sysdba
2. Execute script
set pages 100
set lines 100
SELECT d.status "Status",
d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99999990D900') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
order by "Free %";
-------------------------------------------------------------------------------
3. A tabled usage of the database will be displayed on the command prompt