Tablespace usage monitoring script

set feedback off 
set pagesize 70; 
set linesize 2000 
set head on 
COLUMN Tablespace format a25 heading 'Tablespace Name' 
COLUMN autoextensible format a11 heading 'AutoExtend' 
COLUMN files_in_tablespace format 999 heading 'Files' 
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace' 
COLUMN total_used_space format 99999999 heading 'UsedSpace' 
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace' 
COLUMN total_used_pct format 9999 heading '%Used' 
COLUMN total_free_pct format 9999 heading '%Free' 
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto' 
COLUM total_auto_used_pct format 999.99 heading 'Max%Used' 
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free' 
WITH tbs_auto AS 
 (SELECT DISTINCT tablespace_name, autoextensible 
 FROM dba_data_files 
 WHERE autoextensible = 'YES'), 
 files AS 
 (SELECT tablespace_name, COUNT (*) tbs_files, 
 SUM (BYTES/1024/1024) total_tbs_bytes 
 FROM dba_data_files 
 GROUP BY tablespace_name), 
 fragments AS 
 (SELECT tablespace_name, COUNT (*) tbs_fragments, 
 SUM (BYTES)/1024/1024 total_tbs_free_bytes, 
 MAX (BYTES)/1024/1024 max_free_chunk_bytes 
 FROM dba_free_space 
 GROUP BY tablespace_name), 
 AUTOEXTEND AS 
 (SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs 
 FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow 
 FROM dba_data_files 
 WHERE autoextensible = 'YES' 
 GROUP BY tablespace_name 
 UNION 
 SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow 
 FROM dba_data_files 
 WHERE autoextensible = 'NO' 
 GROUP BY tablespace_name) 
 GROUP BY tablespace_name) 
SELECT c.instance_name,a.tablespace_name Tablespace, 
 CASE tbs_auto.autoextensible 
 WHEN 'YES' 
 THEN 'YES' 
 ELSE 'NO' 
 END AS autoextensible, 
 files.tbs_files files_in_tablespace, 
 files.total_tbs_bytes total_tablespace_space, 
 (files.total_tbs_bytes - fragments.total_tbs_free_bytes 
 ) total_used_space, 
 fragments.total_tbs_free_bytes total_tablespace_free_space, 
 round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes) 
 / files.total_tbs_bytes 
 ) 
 * 100 
 )) total_used_pct, 
 round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100 
 )) total_free_pct 
 FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto 
WHERE a.tablespace_name = files.tablespace_name 
 AND a.tablespace_name = fragments.tablespace_name 
 AND a.tablespace_name = AUTOEXTEND.tablespace_name 
 AND a.tablespace_name = tbs_auto.tablespace_name(+) 
order by total_free_pct;

One thought on “Tablespace usage monitoring script

  1. Rohan

    Its really very use full script to check the tablespace utilizations in details for oracle database.
    thanks to posting.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *