We can use the below queries for getting the details for Table Space.

Table Spaces Details

Oracle 10g and above

select a.tablespace_name,a.bytes/1024/1024/1024 as Total_Space,b.bytes/1024/1024/1024 as Free_Space from sm$ts_avail a,sm$ts_free b
where a.tablespace_name=b.tablespace_name
          and b.tablespace_name='APPS_TS_QUEUES' 
order by 3;

Oracle 9i and below

select b.tablespace_name, tbs_size SizeGb, a.free_space FreeGb
from  (select tablespace_name, sum(bytes)/1024/1024/1024 as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 3;

Getting details for free space in Table Spaces

select TABLESPACE_NAME ,sum(bytes)/1024/1024 
from dba_free_space 
group by TABLESPACE_NAME;

Finding default and temporary table space details for a user in Database

select default_tablespace,temporary_tablespace 
from dba_users  
where username='&user_name';

Adding data file in Table Space

ALTER TABLESPACE USER_TABLESPACE ADD DATAFILE '/oradata03/DEV/applsysd013.dbf' size 12287M;

Note: Size can added in MB not in GB.

Adding data file to Temp table space.


Below query will give the free and used space used by temp table space.


 SELECT tablespace_name,sum(bytes_free)/1024/1024,sum(bytes_used)/1024/1024
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used
     
In 11g we need to use below query to find the temp free space

select tablespace_name, tablespace_size/1024/1024 "Total Space MB",allocated_space/1024/1024 "Alloc Space MB",free_space/1024/1024 "Free Space MB"
from dba_temp_free_space;
    
We can use the below query to find the table space file names and it size.
       
Select FILE_NAME,tablespace_name,sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name,FILE_NAME;


FILE_NAME                                          TABLESPACE_NAME                SUM(BYTES)/1024/1024
-------------------------------------------------- ------------------------------ --------------------
/tempdata/temp001.dbf                       TEMP                                          10000
/tempdata/temp002.dbf                       TEMP                                          10000





Adding datafile to temp table space
 
ALTER TABLESPACE TEMP ADD TEMPFILE '/tempdata/temp003.dbf'  size 10000m;