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

RMAN: restore database skip tablespace

Sometimes we want to restore full database except a certain tablespace (data not needed, or a point in time recovery omiting a big tablespace, etc.). That means we need to skip the tablespace(s) for restore operation. This way, the restore operation time can be much quicker.

Remember that the RESTORE DATABASE command restores all the datafiles, except the offline or read-only tablespaces.

Note that RESTORE DATABASE is not the same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.

To omit a certain tablespace for restore operation use:

RESTORE DATABASE SKIP TABLESPACE tablespace_name.

Suppose I want to omit the restore of indexes tablespace INDX01,INDX02. Then my restore command will be:

RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02;

If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore.

The DROP option indicates that RMAN does not intend to recover these files and instead remove their tablespaces from the database's rman operations after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.

Suppose you want to skip forever the restore of the tablespaces EXAMPLE, INDX01 and INDX02 then your command will be:

RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;