Oracle Tablespace-05



A database is divided into one or more logical storage units called tablespaces.

All tablespaces, including the SYSTEM tablespace, can be locally managed.


Types: 1.System tablespace 2. Non System tablespace





— A different block size object cannot move between the tablespace.

— All the data file name should be created as unique. Since, it might have some issues in cloning a database.

TEMPFILEs are not fully initialized (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database’s control file. This implies that one can just recreate them whenever you restore the database.


Query to get tablesapce space:

sql> select * from v$tablespace;

sql> select * from dba_tablespaces;

Difference between V$ view and DBA

Dynamic view Data dictionary view
It can be queried in mount and open It can only queried in open
It will not have ‘s’ at the end of the  command It will  have ‘s’ at the end of the  command

Create new TableSpace

sql> create tablespace <ts ame> datafile ‘\opt\oracle.dbf’ size 100m;

Query to get a data file info

sql> select * from v$datafile;

sql> select * from dba_data_files;


Query to get a table space growths

Total – DBA_data_files;

sql> select tablespace_name, sum(bytes/1204/1024 from dba_data_files group by tablesspace_name;

Used –  DBA_segment;

Free –  DBA_free_space;


Expand the Tablespace size:

There are two options are available, either you can add one more data file or expand the existing data file. The Max size of each data file is 32G.


sql> alter tablespace <Ts name> add datafile ‘opt/oracle/_3.dbf’ size 100m;

sql> alter database datafile ‘opt/oracle/_3.dbf’ resize 200m;


Table space mode:  1.Read only  2.Read Write

sql> alter tablespace <Ts name> read only;

sql> alter tablespace <Ts name> read write;

sql> alter tablespace <Ts name> offline;

sql> alter tablespace <Ts name> online;

Data file Offline:

This is mainly used to recover the data file from corruption.

To take the data file offline, should first take the tablespace offline and then data file.

sql> alter tablespace <Ts name> offline;

sql> alter database datafile ‘opt/oracle/.dbf’ offline; — In Archive / no Archive mode

sql> alter database datafile ‘opt/oracle/.dbf’ offline drop; — No archive mode

‘offline Drop’ will not drop the files the data files physically, it will remove the details from  control file.


Rename the tablespace:

sql> alter tablespace <ts name> rename to ‘new name’;

Data file rename and move: It’s a physical one needs down time

sql> alter tablespace <ts name> offline;

$ cd /opt/oracle/

$ ls

$ cp tools.dbf tools_new_01.dbf

sql> alter tablespace < ts name > rename datafile ‘old full location’ to  ‘new full location’;




RENAME DATAFILE ‘/u02/oracle/rbdb1/users01.dbf’,’/u02/oracle/rbdb1/users02.dbf’

TO ‘/u03/oracle/rbdb1/users01.dbf’,’/u04/oracle/rbdb1/users02.dbf’;

Auto extent:

By default it will be off. You can enable it, oracle will increase this by 1 Mb, 1MB so on.

sql> alter database datafile ‘opt/oracle/.dbf’ autoextend on;



ADD DATAFILE ‘/u02/oracle/rbdb1/users03.dbf’ SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;

Create tablespace with auto extend on

sql> create tablespace <ts name> ‘opt/oracle/.dbf’ size 100m  autoextend on;

Drop tablespace:

sql> Drop tablespace <ts name> including contents

“including contents” It only remove the content not the  physical file.

sql> Drop tablespace <ts name> including contents and datafiles;

Optional –“ including contents and datafiles “, will remove the files physically. It’s always good to use with include command.


SQL> set linesize 3000



System TableSpace:


It’s a source of the database. (Data dictionary view and dynamic view, default, SPs, Functions is stored here.)

We can create a LMT and can migrate the DMT to LMT. If the system is in LMT.

SysAUX:  (10g Onwards)

It’s an auxiliary tablespace to the SYSTEM tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail.

AWR and the OEM repository task are processed here.

Other components: Oracle streams, ultra search & oracle text all are processed here.


It contains temporary data that persists only for the duration of the session.

No permanent data. All the sorting processed here.

No backup needed, we can rename & relocate and drop the data files.

A default temporary cannot be taken off and drop. Sorting will not run and it will send an error. By default 1 temporary tablespace will be created. We can create many and can assign one per user.

In case if it is full, you can shrink the tablespace or data file.

Create temporary tablespace

Create temporary tablespace temp tempfile ‘/opt/xx.tmp’ size 100m;

Find the tempfile

Select * from v$tempfile;

select * from dba_temp_files;


Find the default temp tablespace

Select * from database_Properties where property_name like ‘DEFAULT’;

SELECT * FROM database_properties WHERE property_name like ‘%TABLESPACE’;


Change the default temp tablespace

Alter database default temporary tablespace temp;


Growth checking of temp tablespace

Select tablespace_name, total_extents, used_extents,free_extents from v$sort_segment;

Best practice, we need to add space, when it reaches 50% threshold.

Add a temp file

Alter tablespace temp add tempfile ‘/opt/xx02.tmp’ size 100m;

Resize the temp file

Alter database tempfile ‘/opt/yy.tmp’ resize 200m;

Drop the temp file

Drop tablespace tmp1 including contents and datafiles;



Undo Tablespace (9i Onwards)

Old records are stored here, until before commit. Flashback features use the Automatic Undo Management system to obtain metadata and historical data for transactions.

Undo will be used in following area. If no undo tablespaces available Oracle uses system tablespace.

Transaction rollback – The rollback records are taken from undo to rollback.

Transaction recovery – It’s a SMON process (Redo process)

Read consistency – The undo will give a read consistency reader do not block writers vice versa.

In some cases, the old records may not be available, if the undo is full it gets overwrite and will send an error : ORA – 01555 snapshot too old.



Undo_management = auto/manual – Default AUTO

Undo-tablespace = undotbs1 – Default name

Undo_Retention = 900 – Seconds


Oracel by default use Auto and you can change if needed.

We can create many undo tablespace. It cannot be assign to specific users.

Create Undo tablespace:

Create undo tablespace name_undo datafile ‘/opt/o1.dbf’ size 100m;


To view the Undo tablespace: – A normal V$ and DBAs table


To find Undo default tablespace info:

Show parameter undo;


To Change default to some other

Alter system set undo_tablespace= undotbs;

Growth Check: We can get this from normal tablespace code.

To drop Undo tablespace:

We cannot drop the default undo. Before drop change the default and do not drop default one

Drop tablespace undotbs1 including contents and data files;


Big Tablespace (10g onwards)


It mostly used for big database – more than 5 TB.

Max datafiles limit 64000

It only supports – 8k & 32K blocks.

For 8K – you can use 32 TB of tablespace and 32k – 128 TB of space.

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three Exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace – Only managed manual.


To find the big tablespace:

Select bigfile from v$tablespace;

Select bigfile from dba_tablespaces;

Create big tablespace:

Create bigfile tablespace <name_bigfile> datafile ‘/opt/big.tbs’ size 100g;

To use the big tablespace, we need to change the normal tablespace into bigfile.

Alter database set default bigfile tablespace;

Alter database default tablespace <name_bigfile>;

If we do not mention in the tablespace creation, Oracle wil automatically use default one i.e either normal tablesapce or bigfile, which is default.

To Change back to normal tablespace:

Alter database set default smallfile tablespace;

Alter database default tablespace <Anytablespace>;

How to resize the tablespace:

Alter tablespace <bigtbs> resize 200g;

The command only for big tablespace

To Drop big tablespace:

Drop tablespace  bigtbs including contents and datafiles;


Types of tablespace

Locally managed tablespace – LMT and dictionary managed tablespace – DMT.

By default from 9i onwards all are LMT.


It can have the extent free space in the bitmap within tablespace and manage local It has to go system tablespace to get extents free info
All the extents are Auto allocated /uniform non – uniform
User friendly No
Less fragmentation More fragmentation
By default, all tablespaces are LMT


Create LMT:

Create tablespace <tools> datafile ‘/opt/01.dbf’ size 100m extent management local uniform size 100k;


Create DMT:


You cannot create a DMT from 9i onwards.


Multiple temporary tablespace (10g onwards)

To use this we can create a group and add more than one temporary tablespace.


To add temporay tablespace:

You can create a new one or add the existing one.

Create temporary tablespace <temp>  tempfile’/opt/01.tmp’ size 100m tablespace group temp_gs;

Alter tablespace <temp1> tablespace group temp_gs;


Find a tablespace in a group:

Select * from dba_tablespace_groups;

Assign a group to a default temporary tablespace:

Alter database default  temporary tablespace temp_gs;

Remove the temporary tablespace from the group:

Alter tablespace temp1 tablespace group ‘ ‘;

A single quotes will remove the tablespace.

To remove the group, you can remove the other tablespaces, but make sure you do not remove the default temporary tablespace. If it’s used change that to some other as default.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

56 + = 58