Adding space on tablespace.

We can  create new tablespaces with the CREATE TABLESPACE command. Before we create the tablespace we should decide below point:

1. How big you wish the tablespace to be.
2. Where you want to put the datafile or datafiles that will be associated with that tablespace.
3. What you want to call the tablespace and the datafiles.

We recommend that you include the following in the datafile name when you create the tablespace:
1. The name of the database
2. The name of the tablespace
3. A number that makes the datafile unique

Select
   fs.tablespace_name                          "Tablespace",
  df.totalspace                               "TOT_SIZE",
  fs.freespace                                "TOT_FREE",
  (round(100 * (fs.freespace / df.totalspace))) pct_Free
from
   (select      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name  ) fs
where   (df.tablespace_name = fs.tablespace_name ) and fs.tablespace_name like '%&TBS%' ORDER BY pct_free DESC ;

Tablespace TOT_SIZE   TOT_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
REPORT_VIEW      500 500    100
UNDOTBS1      446 411     92
SYSAUX      500 191     38
SYSTEM      500 187     37
XXX_AC_TEST     7500 727     10
XXX_AC_XXX     7500 191      3

6 rows selected.
---Here it showing we need space on XXX_AC_xxx tablespace.

SQL> /
Enter value for tbs: XXX_AC_xxx
old  21: where (df.tablespace_name = fs.tablespace_name ) and fs.tablespace_name like '%&TBS%' ORDER BY pct_free DESC
new  21: where (df.tablespace_name = fs.tablespace_name ) and fs.tablespace_name like '%XXX_AC_DEMO%' ORDER BY pct_free DESC

Tablespace TOT_SIZE   TOT_FREE   PCT_FREE
------------------------------ ---------- ---------- ----------
XXX_AC_DEMO     7500 191      3

-- Here we are going to check datafile available name,

SQL> select FILE_NAME,bytes/1024/1024 from dba_data_files where TABLESPACE_NAME ='&TABLESPACE_NAME';
Enter value for tablespace_name: XXX_AC_XXX
old   1: select FILE_NAME,bytes/1024/1024 from dba_data_files where TABLESPACE_NAME ='&TABLESPACE_NAME'
new   1: select FILE_NAME,bytes/1024/1024 from dba_data_files where TABLESPACE_NAME ='XXX_AC_XXX'

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
/shared/XXXREPORT/fro_ac_demo01.dbf
  7500
--- Verfiy avilable space into Mountpoint -

SQL> !df -h /shared/XXXREPORT/fro_ac_demo01.dbf
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdc3             141G   58G   77G  43% /shared
---- checking new datafile name to be eliminate duplicate datafile name into db-
SQL>  select TABLESPACE_NAME,FILE_NAME from dba_data_files where FILE_NAME like '%&file_name';
Enter value for file_name: fro_ac_demo02.dbf
old   1:  select TABLESPACE_NAME,FILE_NAME from dba_data_files where FILE_NAME like '%&file_name'
new   1:  select TABLESPACE_NAME,FILE_NAME from dba_data_files where FILE_NAME like '%fro_ac_demo02.dbf'

no rows selected

--- Below i am adding new datafile into Tablespace-
SQL> alter tablespace XXX_AC_TEST add datafile '/shared/XXXREPORT/fro_ac_test02.dbf' size 1G;

Tablespace altered.

SQL>