Table Space addition in Oracle Database

Default location for Oracle-managed datafiles:

show parameter db_create_file_dest;

Displays Space Usage for Each Datafile

Set lines 999 pages 100

select

a.tablespace_name,

trunc(SUM(a.bytes)/1024/1024/1024,2) "CurGb",

trunc(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*8192/1024/1024/1024)),2) "MaxGb",

trunc((SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024)),2) "TotalUsedGb",

trunc((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*8192/1024/1024/1024)) - (SUM(a.bytes)/1024/1024/1024 - round(c."Free"/1024/1024/1024))),2) "TotalFreeGb",

round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"

from

dba_data_files a,

sys.filext$ b,

(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,

DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c

where a.file_id = b.file#(+)

and a.tablespace_name = c.tablespace_name

GROUP by a.tablespace_name, c."Free"/1024/1024

order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null,

A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc

/

Add Data file in a Table Space

alter tablespace DB_TS_Name add datafile size 4G autoextend on;

or 

alter tablespace users ADD DATAFILE    '/u05/oracle/oradata/booktst_users_02.dbf'
size 1024M;