Oracle Database Tablespace Full Alert

STEP 01: Check Space Utilization of Tablespace USing Below Query

set lines 222 pages 333

select df.tablespace_name "Tablespace",
totalusedspace "Used GB",
(df.totalspace - tu.totalusedspace) "Free GB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1073741824) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and tu.tablespace_name='REPLACE_YOUR_TABLESPACE_NAME';

STEP 02: Check Already Added Datafiles Names, Path, Size, Autoextend Details: 

col file_name for a77

select file_name,bytes/1024/1024/1024 GB,autoextensible from dba_data_files where tablespace_name='&table_space_name';

STEP 03: Check Space on Mount Point or ASM Disk Group: 

Non-ASM File System:

    $df -h /u501

ASM File System:

    $asmcmd lsdg

STEP 04: Add Datafile to Tablespace: 

Non-ASM File System:

alter tablespace <tablespace_name> add datafile '/u505/datafile055.dbf' Size 1G autoextend on next 100m maxsize unlimited;

ASM File System:

alter tablespace <tablespace_name> add datafile '+DATA' size 1g autoextend on next 100m maxsize unlimited;

STEP 05: Verify Space Utilization once Files Get Added: 

set lines 222 pages 333

select df.tablespace_name "Tablespace",
totalusedspace "Used GB",
(df.totalspace - tu.totalusedspace) "Free GB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1073741824) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and tu.tablespace_name='REPLACE_YOUR_TABLESPACE_NAME';