How to check tablespace with low space alert.

********************************

Query to find Tablespace utilization:

********************************

set lines 300 pagesize 300

compute sum of "TOTAL SIZE in MB" on report

compute sum of "ALLOCATED SIZE in MB" on report

compute sum of "USED %" on report

compute sum of "FREE %" on report

select a.tablespace_name,

    round(nvl(a.total,0)) "TOTAL SIZE in GB",

    round(nvl(a.asize,0)) "ALLOCATED SIZE in GB",

    round(nvl(a.asize-nvl(f.free,0),0)) "USED",

    round(nvl(a.total-a.asize+f.free,0)) "FREE",

    nvl(f.maxfree,0) "MAX_FREE",

    round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",

    round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %"

    from (select tablespace_name,sum(bytes)/1024/1024/1024 "ASIZE",sum(case when maxbytes > bytes

   then maxbytes else bytes end)/1024/1024/1024 total from dba_data_files group by tablespace_name) a,

   (select tablespace_name, round(sum(bytes/(1024*1024*1024))) free,round(max(bytes)/1024/1024/1024) maxfree

   from dba_free_space group by tablespace_name) f

   WHERE a.tablespace_name = f.tablespace_name(+)

   and a.tablespace_name='&Tablespace'

   order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)

   /

========================================================================

Enter value for tablespace: USERS

old  14:    and a.tablespace_name='&Tablespace'

new  14:    and a.tablespace_name='USERS'

TABLESPACE_NAME TOTAL SIZE in GB ALLOCATED SIZE in GB  USED  FREE MAX_FREE  USED % FREE %
--------------- ---------------- -------------------- ------ ----  -----    ------ ------
USERS            32                  0           0      32     0         0     100

**********************************************
Datafiles In Tablespace on Disk group or filesystem
**********************************************

set pagesize 300

set lines 300

col TABLESPACE_NAME for a15

col FILE_NAME for a60

col Size_GB for 9999999

select 

TABLESPACE_NAME,

FILE_ID,

FILE_NAME,

BYTES/1024/1024/1024 Size_GB,

MAXBYTES/1024/1024/1024 MAXBYTES,

AUTOEXTENSIBLE 

from 

dba_data_files

where 

TABLESPACE_NAME ='&Tablespace_Name'

order by 

FILE_NAME;

========================================================================

TABLESPACE_NAME FILE_ID FILE_NAME                                         SIZE_GB   MAXBYTES AUT

--------------- ------- ------------------------------------------------- -------- ---------- ---

USERS                 4 +DELL_DATA01/prod/datafile/users.259.921999959      0      31.9999847 YES

*****************

ASM_Disk_Uses

*****************

set pages 400 lines 200

col NAME for a15

col STATE for a15

col FREE_MB for 9999999999

select 

GROUP_NUMBER,

NAME,

ALLOCATION_UNIT_SIZE,

STATE,

TYPE,

TOTAL_MB/1024 "Totalspace(GB)",

FREE_MB/1024 "Freespace(GB)",

(free_mb/total_mb)*100 "Pct_FREE_%",

OFFLINE_DISKS

from 

v$asm_diskgroup;

========================================================================

GROUP_NUMBER NAME            ALLOCATION_UNIT_SIZE STATE           TYPE   Totalspace(GB) Freespace(GB) Pct_FREE_% OFFLINE_DISKS

------------ --------------- -------------------- --------------- ------ -------------- ------------- ---------- -------------

           1 DELL_DATA01                  1048576 CONNECTED       NORMAL     11.9960938    7.99609375 66.6558124             0

           2 FRA                          1048576 MOUNTED         EXTERN     11.9960938    11.9472656 99.5929665             0

*********************************

To Add Datafile In Tablespace

*********************************

ALTER TABLESPACE NFL_TSQ02 ADD DATAFILE '+DATA_DELL/' SIZE 100m AUTOEXTEND ON NEXT 20m MAXSIZE 32767m