Oracle Database - Managing Tablespaces

In an Oracle database, a tablespace is a logical storage unit that stores all of the database's data. Tablespaces are made up of one or more physical structures called datafiles, which conform to the operating system that Oracle is running on

	SQL> select tablespace_name from dba_tablespaces;

	TABLESPACE_NAME
	------------------------------
	SYSTEM
	SYSAUX
	TEMP
	USERS
	UNDOTBS

When a new database is created, Oracle automatically creates several tablespaces, including:

SYSTEM : Contains the data dictionary, which includes tables' definitions, views, and stored procedures

SYSAUX : A default tablespace

USERS : A default tablespace

UNDOTBS : A default tablespace that stores undo information

TEMP : A default tablespace

To find datafiles associated with tablespaces and size :

	SQL> select tablespace_name,file_id,file_name,bytes/1024/1024 MB from dba_data_files

	TABLESPACE_NAME 		  FILE_ID FILE_NAME						     MB
	------------------------------ ---------- -------------------------------------------------- ----------
	SYSTEM					1 /u01/app/oracle/oradata/dev/system01.dbf		    810
	SYSAUX					3 /u01/app/oracle/oradata/dev/sysaux01.dbf		    530
	USERS					7 /u01/app/oracle/oradata/dev/users01.dbf		      5
	UNDOTBS1				4 /u01/app/oracle/oradata/dev/undotbs01.dbf		     65

To find temp files associated with a temp tablespace :

SQL> select file_name, bytes/1024/1024 MB from dba_temp_files

FILE_NAME						   MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/dev/temp01.dbf			   32

To create new tablespace :

	SQL> Create tablespace tesdb datafile '/u01/app/oracle/oradata/dev/tesdb01.dbf' size 50m;

	Where

	tesdb is the name of new tablespace

	/u01/app/oracle/oradata/dev/tesdb01.dbf is the location of the datafile

	50m is the size of the datafile

Add Space to Tablespace :
There are two ways to add space to a tablespace:

Resize existing datafile

Add new datafile

Use below command to resize a datafile :

	SQL> Alter database datafile '/u01/app/oracle/oradata/dev/tesdb01.dbf' resize 100m;

	(or)

	SQL>Alter database datafile < file_id> resize 100m;

Use below command to add new datafile to tablespace :

	SQL> Alter tablespace tesdb add datafile '/u01/app/oracle/oradata/dev/tesdb02.dbf' size 50m;

Drop Tablespace :
Below command will drop tablespace including all its contents and associated datafiles

	SQL> drop tablespace tesdb including contents and datafiles;

To check the size of the database :

	SQL> select sum(bytes/1024/1024) MB from dba_data_files;

		MB
	    ----------
		1410

To check each tablespace size :

	SQL> select tablespace_name,sum(bytes/1024/1024) MB from dba_data_files group by tablespace_name;

	TABLESPACE_NAME 		       MB
	------------------------------ ----------
	SYSAUX				      530
	UNDOTBS1			       65
	USERS					5
	SYSTEM				      810
	TESDB                                 150

To check free space in each tablespace :

	SQL> select tablespace_name,sum(bytes/1024/1024) MB from dba_free_space group by tablespace_name;

	TABLESPACE_NAME 		       MB
	------------------------------ ----------
	SYSAUX				    48.25
	UNDOTBS1			   29.375
	USERS					3
	SYSTEM				    2.625

To check database free space :

	SQL> select sum(bytes/1024/1024) MB from dba_free_space;

		MB
	----------
		83.25

To check the total used or allocated space in tablespace :

	SQL> select sum(bytes/1024/1024) MB from dba_segments;

		MB
	----------
	1322.75

To change tablespace into offline mode :

	SQL>alter tablespace tesdb offline;

To change tablespace into online :

	SQL>alter tablespace tesdb online;

Undo tablespace- (read consistency) :
An undo tablespace is a logical database structure that stores information used to roll back changes to the database. Oracle saves undo data until the transaction has been committed.

	SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/dev/undo_01.dbf' size 500m;

To change the old undo into new undo :

	SQL> alter system set undo_tablespace=undotbs1 scope=spfile;

To drop old undo tablespace :

	SQL>drop tablespace undotbs including content and datafiles;

Advantages Of Temporary Tablespace :

A Temporary Tablespace is used to manage space for sort operations.
SQL queries are less likely to run out of space.
Database specify multiple default temporary tablespaces at the db level
Parallel execution can utilize multiple temporary tablespaces.
Single user can simultaneously use multiple temp tablespaces in different sessions.
Used for Sort Operations Cannot contain any Permanent Objects.
Locally Managed Extents recommended.
Tempfiles are also in a NOLOGGING Mode.
Tempfiles cannot be made read only or be renamed.
Tempifiles are required for read-only databases.

Step 1 : Creating the temp tablespace
Create temporary tablespace temptbs3 tempfile '/u01/app/oracle/oradata/TES/temp03.dbf' size 100m;
Tablespace created.

Step 2 : Checking the newly created temp tablespace

	   select file_id,tablespace_name,bytes/1024/1024 "Size"
	   from dba_temp_files;

	   	FILE_ID		TABLESPACE_NAME		Size
		--------	-------------------	-------
		 1		TEMP			32
		 2		TEMPTBS			50
		 3		TEMPTBS3		100

Step 3 : Set the newly created temp tablespace as default temporary table space for the database

	SQL> alter database default temporary tablespace temptbs3;
	Database altered.

	SQL> select * from database_properties where property_name=
	'DEFAULT_TEMP_TABLESPACE';

	PROPERTY_NAME			PROPERTY_VALUE	DESCRIPTION
	------------			--------------	------------
	DEFAULT_TEMP_TABLESPACE		TEMPTBS3	Name of default temporary
							tablespace

Step 4 : Make the temp tablespace to autoextend based on data

	alter database tempfile '/u01/app/oracle/oradata/TES/temp03.dbf'
	autoextend on;

	Database altered.

	select file_name, autoextensible from dba_temp_files;

	FILE_NAME				AUT
	-------------------------------------	---
	/u01/app/oracle/oradata/TES/temp01.dbf	YES

	/u01/app/oracle/oradata/TES/temp02.dbf	YES

	/u01/app/oracle/oradata/TES/temp03.dbf	YES

Step 5 : Resize the temp tablespace

	SQL>  alter database tempfile 2 resize 80m;
	Database altered.

	 SQL> select file_id,tablespace_name,bytes/1024/1024 "Size"
	 from dba_temp_files

	   FILE_ID TABLESPACE_NAME			Size
	---------- ------------------------------ ----------
		 1 TEMP 				  32
		 2 TEMPTBS				  80
		 3 TEMPTBS3				 100

Step 6 : Setting the tablespaceto offline

	SQL> alter database tempfile
	'/u01/app/oracle/oradata/TES/temp03.dbf' offline;

	Database altered.

	SQL> alter database rename file
	'/u01/app/oracle/oradata/TES/temp03.dbf' to
	'/u01/app/oracle/oradata/TES/temp04.dbf';

	Database altered.

Step 7 : Setting the tablespaceto online

	SQL> alter database tempfile
	'/u01/app/oracle/oradata/TES/temp03.dbf' online;

	SQL> select file_name, autoextensible from dba_temp_files;
	FILE_NAME					AUT
	---------------------------------------	---
	/u01/app/oracle/oradata/TES/temp01.dbf	YES

	/u01/app/oracle/oradata/TES/temp02.dbf	YES

	/u01/app/oracle/oradata/TES/temp04.dbf

	SQL>  select file_id, tablespace_name, status from dba_temp_files;

	   FILE_ID	TABLESPACE_NAME		  	STATUS
	---------- 	--------------------- 		-------
		 1	TEMP				ONLINE
		 2	TEMPTBS				ONLINE

Step 8 : Drop the temp tablespace

	SQL> alter database tempfile 2  drop;

	SQL>  select file_id, tablespace_name, status from dba_temp_files;

	   FILE_ID		TABLESPACE_NAME			STATUS
	-------- 		------------------ 		-------
		 1		TEMP				ONLINE

« Previous Next Topic » ( Oracle Database - Space & Storage Parameters)