TABLESPACE USAGE NOTES

 

Usage

TABLESPACE USAGE NOTES:

1. Tablespace Name - Name of the tablespace
2. Bytes Used - Size of the file in bytes
3. Bytes Free - Size of free space in bytes
4. Largest - Largest free space in bytes
5. Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%

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