TABLESPACE Monitoring

Useful SQLs to monitor tablespace

Find the Tablespace which having less than 5% space left

This query will give the output for APPS_TS_TX_DATA & APPS_TS_TX_IDX tablespaces:
select trunc(sysdate), c.name, b.tablespace_name, tbs_size, tbs_size - a.free_space, a.free_space, round(a.free_space / tbs_size * 100, 0) per_free
from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b, (select name from v$database) c
where a.TABLESPACE_NAME(+)=B.TABLESPACE_NAME and a.TABLESPACE_NAME like 'APPS_TS_TX%' and round(a.free_space / tbs_size * 100, 0) <= 5 order by 6;

This query will give the output for all tablespaces:
select trunc(sysdate), c.name, b.tablespace_name, tbs_size, tbs_size - a.free_space, a.free_space, round(a.free_space / tbs_size * 100, 0) per_free
from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b, (select name from v$database) c
where a.TABLESPACE_NAME(+)=B.TABLESPACE_NAME and round(a.free_space / tbs_size * 100, 0) <= 5 order by 6;

Tablespace Percentage(%) Used

It will give How much Percentage(%) is already used for a Particular Tablespace

SET LINESIZE 200

SELECT A.TABLESPACE_NAME, ROUND(A.BYTES/1024/1024) "TOTAL", ROUND(B.BYTES/1024/1024) "USED", ROUND(C.BYTES/1024/1024) "FREE",

ROUND((B.BYTES*100)/A.BYTES) "% USED", ROUND((C.BYTES*100)/A.BYTES) "% FREE"

FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME AND A.TABLESPACE_NAME='&PLEASE_PROVIDE_TABLESPACE_NAME';

Add Datafile to Tablespace

SELECT * FROM dba_tablespace_usage_metrics ORDER BY used_percent;

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = 'APPS_TS_TX_DATA'; 

SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='<TS_NAME>' ORDER BY FILE_NAME;

ALTER TABLESPACE <TABLE_SPACE_NAME> ADD DATAFILE '<DBF_NAME_WITH_LOCATION>' SIZE 4096M;

ALTER TABLESPACE <TABLE_SPACE_NAME> ADD DATAFILE '<DBF_NAME_WITH_LOCATION>' SIZE 4096M autoextend on;

Alter database datafile '/xxxxx/xxxx/zpbdxx.dbf' autoextend on;

select ts.name||'|'||df.name||'|'||bytes/1024/1024||'|'||CREATE_BYTES/1024/1024 from v$tablespace ts, v$datafile df where ts.ts#=df.ts# and ts.name ='&TABLESPACE_NAME';

ALTER TABLESPACE <TS_NAME> ADD DATAFILE '<DBF_NAME_WITH_PATH>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/UAT4/oracle/db/apps_st/data/APPS_TS_TX_IDX_002.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

Add Datafile to Tablespace Using Autoextent ON

ALTER TABLESPACE <TS_NAME> ADD DATAFILE '<DBF_NAME_WITH_PATH>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/UAT4/oracle/db/apps_st/data/APPS_TS_TX_IDX_002.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

Resize Datafile

SQL> alter database datafile '/prod/oradata/custom01/prod/ts_custom_data13.dbf' resize 4096m;

SQL> alter database datafile '/prod/oradata/custom01/prod/ts_custom_data13.dbf' modify autoextend by 50m;