TABLESPACE ORACLE DATABASE GUIDE

  SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       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
 order by 5 desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
for getting usage for only given tablespace
---------------------------------------------------------------
SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       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
 and fs.tablespace_name='&tablespace_name'
 GROUP BY df.tablespace_name,df.bytes
 order by 5 desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#TEMP TABLESPACE ######################
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_GB;
=========================================================================
select file_name,autoextensible,sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='&tablespace_name' group by file_name,autoextensible order by file_name desc;
=========================================================================
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
=========================================================================
select tablespace_name, bytes / (1024*1024*1024) "Size (GB) from dba_data_files;
for resize datafile :
alter database datafile 'path' resize 4G ;
for adding datafile :
alter tablespace tablespace_name add datafile 'path' size 3G autoextend off ;


################################ FOR RAC DB ################
Add or Resize Datafile in RAC database


alter tablespace tablespace_name add datafile '+ASM_NAME' size nG autoextend on ;
alter tablespace tablespace_name add datafile '+ASM_NAME' size nG autoextend off;


alter database datafile '+/path/to/datafile' resize nG;