TABLESPACE MANAGEMENT IN ORACLE

When any space issues comes you as a DBA have to add some datafiles to the respective tablespace.

Here is the list of commands(queries) in detail how to know the detail of the Tablespace and add datafile to it.

This is an example query that will display free and used space for each tablespace in a database.
_________________________________________________________________________________

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       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
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

_________________________________________________________________________________
After getting the information you will proceed with adding datafiles to the Tablespace.

SQL> select * from dba_tablespace_usage_metrics  WHERE TABLESPACE_NAME='<Tablespace_name>';


Find the datafiles associated with table space using below query

SQL> col TABLESPACE_NAME for a45
col FILE_NAME for a50
select TABLESPACE_NAME ,FILE_NAME,BYTES/1024/1024 from dba_data_files where TABLESPACE_NAME like '&TABLESPACE_NAME' order by 2;

Use below queries to add datafiles to tablespace .

Check the squence of the datafiles last added and now add with the next sequence.
Ex-If datafile added with 20.dbf ,u can add with 21.dbf next.

SQL> alter tablespace Tablespace_name add datafile '<Path to datafile>' size 400M  autoextend on next 100M maxsize 5000M;

Tablespace altered.

Now check and verify again if the datafiles has been added successfully or not.

For ASM Instances:

SQL> select * from dba_tablespace_usage_metrics  WHERE TABLESPACE_NAME='DWOWNER_DATA_01';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

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

DWOWNER_DATA_01                 157270080       157286400    99.989624


To ADD DATAFILE

SQL> ALTER TABLESPACE DWOWNER_DATA_01 ADD DATAFILE '+DATA' SIZE 20G AUTOEXTEND ON NEXT 100M MAXSIZE 5000M;

To RESIZE DATAFILE

SQL> ALTER DATABASE DATAFILE '+DATA/titu1o/datafile/dwowner_data_01.363.865931253' RESIZE 1500G;

Database altered.