Tablespace Info

Basic form for Adding Datafile to a Tablespace

ALTER TABLESPACE "TABLESPACE_NAME" 
    ADD 
    DATAFILE '/path/filename.dbf'  SIZE  
    100M REUSE AUTOEXTEND 
    ON NEXT  10M MAXSIZE  65535M;

Recommend size to shrink datafiles to based on unused(free) space

select ddf.file_name,
       dfs.file_id,
       ddf.blocks,
       (ddf.blocks*value)/1024/1024 file_size_mb,
       dfs.block_id block_hwm,
       ddf.blocks-dfs.block_id fat_blocks,
       floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
       ceil(((ddf.blocks*value)/1024/1024 -
         ((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
  from dba_free_space dfs,
       dba_data_files ddf,
       v$parameter
where v$parameter.name = 'db_block_size' 
   and (ddf.tablespace_name = UPPER('&&table_space') 

        or 'ALL' = UPPER('&&table_space')) 

   and dfs.tablespace_name = ddf.tablespace_name
   and dfs.file_id = ddf.file_id
   and dfs.block_id = (select max(block_id)
                         from dba_free_space
                        where file_id = dfs.file_id)

  order by fat_blocks desc; 

Display the percentage of free space in a tablespace

set pagesize 99
column total_space format 999,999,999,999
column free_space format 999,999,999,999
column pct_free format 999.99
select a.tablespace_name, total_space, free_space, free_space/total_space*100 pct_free
from
(select tablespace_name, sum(bytes)/1024/1024 total_space
 from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 free_space
 from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+)
order by pct_free;