Tablespace usage

Tablespace Utilization Script for Tablespace Space Used % more than 80 %

Oracle Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)

1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.

1. Check the database details.
$ sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;

2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

3. Check the details of the datafiles for a particular TableSpace which needs attention.

Datafiles of a particular TableSpace:

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

set pages 50000 lines 32767

col tablespace_name for a30

col CREATION_TIME for a15

col file_name for a70

select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME

from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;

file size 

set lines 150

column file_name format a80 

column MB format 999,999,999

column MAXMB format 999,999,999

select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB

from dba_data_files

where tablespace_name = upper('&ts_name');

set lines 500

set pages 500

column ts format a8

column tablespace_name format a30

column allocated format 999,999,999

column unallocated format 999,999,999

column used format 999,999,999

column allocated_free format 999,999,999

column total_free format 999,999,999

column allocated_used_pct format 990.00

column total_used_pct format 990.00

select 'TS' as TS,

       ts.tablespace_name, 

       allocs.allocated allocated, 

       trunc(free.MB) allocated_free,  

       allocated-trunc(free.MB) used,

       allocs.unallocated unallocated, 

       (allocs.unallocated+trunc(free.MB)) total_free,

       trunc(((allocs.allocated - trunc(free.MB))/(allocs.allocated))*100,2) allocated_used_pct,

       trunc((allocs.allocated - trunc(free.MB))/(allocated+unallocated)*100,2) total_used_pct

from dba_tablespaces ts 

inner join

(select tablespace_name, sum(allocated) allocated, sum(unallocated) unallocated

from

   (select tablespace_name, sum(bytes)/1024/1024 allocated, sum(maxbytes-bytes)/1024/1024 unallocated

   from dba_data_files

   where autoextensible = 'YES'

   group by tablespace_name

   union 

   select tablespace_name, sum(bytes)/1024/1024 allocated, 0

   from dba_data_files

   where autoextensible = 'NO'

   group by tablespace_name) inner_allocs

group by tablespace_name) allocs

on ts.tablespace_name = allocs.tablespace_name

left join (select tablespace_name, sum(bytes)/1024/1024 MB

           from dba_free_space

           group by tablespace_name) free

on ts.tablespace_name = free.tablespace_name 

where ts.contents = 'PERMANENT'

order by 9 desc;