Tablespace Usage and Add-resize Datafile

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

In day to day work  dba or developer come across tablespace alerts regarding usage spike to 90% or above threshold value, so in this case we have options like adding one more datafile to the tablespace or truncating the tables or objects in tablespace or more often resizing the datafile , these options do well and kind of solve the issue but there is a option in adding datafile like "autoextend on" so database will take care of the usage and will reserve disk space automatically when tablespace needed it. 

To see the tablespace usage execute following query :

 SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024 * 1024) "Size (GB)",

       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

 order by 5 desc;

After you from above query result which tablespace usage has spike then run the following to know datafiles inside that tablespace so you can add or resize datafile :

select file_name,autoextensible,sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='GGS_DATA' group by file_name,autoextensible;

To resize a datafile :

alter database datafile '/path/to/datafile.dbf ' resize nG ;   .......................(n is no of GB eg. 5G) 

To add datafile :

alter tablespace tablespace_name add datafile '/path/to/datafile.dbf ' size nG autoextend on 

alter tablespace tablespace_name add datafile '/path/to/datafile.dbf ' size nG autoextend off;

 SELECT /* + RULE */  

(select sys_context('userenv','service_name') from dual) service_name,

df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024 * 1024) "Size (GB)",

       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

 order by 5 desc;