Tablespace Usage Monitoring and Maintenance

Few Useful SQLs for Tablespace Usage Monitoring and Maintenance

• Space Usage for Each Datafile (When auto-extend is ON)

SELECT 
tablespace_name, 
file_name, 
ROUND(bytes/1024/1024/1024,1) SIZE_GB, 
ROUND(maxbytes/1024/1024/1024,1) max_GB, 
autoextensible,
ROUND(bytes/maxbytes*100) percent_used
FROM 
dba_data_files 
ORDER BY bytes/maxbytes*100 DESC;

• Space Usage for Each Tablespace (When auto-extend is ON)

SELECT 
tablespace_name, 
ROUND(SUM(bytes)/1024/1024/1024,1) SIZE_GB, 
ROUND(SUM(maxbytes)/1024/1024/1024,1) max_GB, 
ROUND(SUM(bytes)/SUM(maxbytes)*100) percent_used
FROM 
dba_data_files 
GROUP by tablespace_name
ORDER BY 4 DESC;

• Tablespace Usage Monitoring (When Auto Extend is ON)

SELECT 

  A.TABLESPACE_NAME, 

  A.GB SEGMENT_USED_GB, 

  B.SIZE_GB ALLOCATED_GB, 

  B.MAX_GB MAX_GB, 

  B.PERCENT_USED PERCENT_ALLOCATED, 

  ROUND(A.GB/B.SIZE_GB*100) PERCENT_USED
FROM
(select tablespace_name, ROUND(sum(bytes)/1024/1024/1024,1) GB from dba_segments group by tablespace_name having ROUND(sum(bytes)/1024/1024/1024,1) > 0 ) A,
(select 
 tablespace_name, 
 ROUND(SUM(bytes)/1024/1024/1024,1) SIZE_GB, 
 ROUND(SUM(maxbytes)/1024/1024/1024,1) max_GB, 
 ROUND(SUM(bytes)/SUM(maxbytes)*100) percent_used
from 
 dba_data_files 
GROUP by tablespace_name
HAVING ROUND(SUM(bytes)/1024/1024/1024,1) > 0) B 

WHERE A.tablespace_name = B.tablespace_name
ORDER BY ROUND(A.GB/B.SIZE_GB*100) DESC;

• Datafile name and Size for a Tablespace(Auto Extend is ON)

set lines 300
col tablespace format A20
col file_name format A60
select tablespace_name, file_name, bytes/(1024*1024*1024) size_GB, autoextensible, maxbytes/(1024*1024*1024) max_GB, increment_by from dba_data_files where tablespace_name='INDEXES';

• Add a New Datafile (With Auto Extend is ON)

ALTER tablespace INDEXES add datafile '/u02/oradata/dnaprod/indexes04.dbf' size 1G autoextend ON maxsize unlimited;