ORACLE: Tablespaces

Tablespaces are where Oracle stores data logically. Each tablespace is consists of datafiles. Datafiles are the physical storage of the data. Please see below common scripts used for tablespace management.


***********************Create Tablespace********************************

CREATE TABLESPACE tablespaceName DATAFILE '<datafile>' SIZE xxM



*******************checked all tablespace usage****************************

select b.tablespace_name, b.total "TOTAL SPACE (MB)",
round((sum(a.bytes)/(1024 * 1024)),2) "Free Space (MB)",
round((sum(a.bytes)/(1024 * 1024))/b.total*100,2) "% Free"
from dba_free_space a,
(select tablespace_name, sum(bytes/1024/1024) total
from dba_data_files group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
group by b.tablespace_name, b.total order by 4;

******************tablespace status****************************************

col file_name format a60;

set lines 150;

set pages 50;

select FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where ONLINE_STATUS='RECOVER';

***********Determine space percentage available for specific tablespace***********

select b.tablespace_name , b.total "Mb Total",  

sum( a.bytes/(1024 * 1024)) "Mb Free",

sum( a.bytes/(1024 * 1024))/b.total*100 "%Free"

from dba_free_space a ,

(select tablespace_name, sum(bytes/1024/1024) total  

from dba_data_files group by tablespace_name) b

where b.tablespace_name = 'tablespaceName'

and a.tablespace_name(+)=b.tablespace_name

group by b.tablespace_name, b.total 

order by 4;

******************Check the datafiles of a tablespace*************************

set linesize 140

col FILE_NAME format a60

select file_name, bytes/1024/1024 as Mb from dba_data_files where tablespace_name = 'tablespaceName' order by 1;

****************Add Datafile to a tablespace********************************

alter tablespace tablespaceName add datafile '<datafile>' size xxM;

************************Resize datafile************************************

alter database datafile '<datafile>'  resize xxM;