Tablespace Creation Script

select 'create tablespace '||tablespace_name||' datafile '''||file_name||''' size  '||sum(bytes)/1024/1024||'M;'
 from dba_data_files where tablespace_name not in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS')
 group by tablespace_name,file_name;

Tablespace Usage

Check free/used space per tablespace


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;


Sample output:
Tablespace                      Size (MB)    Free (MB)   % Free     % Used
------------------------------   ------------  ------------    ---------- ----------
UNDOTBS1                             65         17.8125      27         73
EXAMPLE                               100        22.625        23         77
USERS                                    5          1.0625       21          79
TEMP                                     20          2              10          90
SYSAUX                                 625        54.5           9           91
SYSTEM                                 700        9.0625        1           99

Tablespace Usage Info (Free Space, Used Space, Percentage)

select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1'; 

How To Get Temporary Tablespace Information

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;

or 

SELECT inst_id "instid", SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment; 


SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';


SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

TNSNAMES.ORA Template

ABCD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = xyz)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = xyz)
    )
  )