Oracle: Manage your tablespaces, useful queries

Show the free available space:

SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  dba_free_space fs,
  dba_tablespaces ts
WHERE fs.tablespace_name(+)   = ts.tablespace_name
and ts.tablespace_name not in ('SYSAUX', 'SYSTEM', 'TEMP', 'TEMPBIG')
and ts.tablespace_name not like '%UNDO%'
GROUP BY
  ts.tablespace_name

Show more details for tablespace size management:

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name 
order by tu.tablespace_name;

Coalesce a tablespace.

Alter tablespace TABLESPACE_NAME coalesce; 

Resize the datafiles to a minimum space.

select 'alter database datafile ''' || file_name || ''' resize ' || CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
from dba_data_files a,
  (select file_id, max(block_id + blocks - 1) hwm from dba_extents group by file_id) b,
  (select to_number(value) blksize from v$parameter where name = 'db_block_size') c
where a.file_id = b.file_id (+)
and CEIL(blocks * c.blksize / 1024 /1024) - CEIL((nvl(b.hwm,1) * c.blksize) / 1024 /1024) > 0
and tablespace_name not in ('SYSTEM','TEMP') and tablespace_name not like '%UNDO%';

See the biggest objects in the SYSTEM tablespace:

  select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_m
 from dba_segments
 where tablespace_name = 'SYSTEM'
 and    bytes/(1024*1024) > 1
 order by size_m desc