What is a tablespace and datafile:

Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(DISKS) . So when we create a datafile of 30G in database, it will eat 30G of space from defined storage. All the table or index segment is created in tablespace only.

Types of Tablespace:

  • Each database contains a SYSTEM tablespace.( It is created by default while creating the database itself) .
  • It contains the data dictionary tables of the database.
  • All the view,procedures, functions, packages and triggers are stored in SYSTEM tablespace
  • It is not recommended to user SYSTEM tablespace for creating any other table or index.
  • All the database metadata, that doesnt store in SYSTEM tablespace, will be stored here.
  • Many database components datas are stored in this tablespace
  • When someone says tablespace, Means they are mostly refering to PERMANENT TABLESPACE
  • This tablespace are used for storing the actual schema tables or indexes . i.e it Stores user data.
  • It doesnt store any of the data dictionary tables.
There are two types of PERMANENT TABLESPACE.
  • SMALL FILE – (DEFAULT) – This is the common size tablespace, It can contain multiple datafiles with each datafile of size 31G;
  • BIG FILE – It can contain only one datafile which can grow upto 128 TB.
  • This tablespace stores Undo data:
  • We can’t create any table or index inside undo tablespace.
  • Each instance in the database have one defined UNDO tablespace. i.e for standalone database one assigned undo tablespace, and for multi node rac system, each instance on the node will have one assigned undo tablespace.
  • This tablespace stores the data temporarily for sessions doing sorting and join operations.
  • Sort operations are also generated by SELECT * FROM WHERE CLAUSE that join rows from within tables and between tables. The segments generated during this process will be stored in temp tablespace.
  • Each database will have one default temporary tablespace


 Create a NEW tablespace:

Here, 500M of extent will be added to the datafile automatically, when the space is required.

For creating a tablespace on ASM disk group:

– With OMF file system:

Adding a datafile:

Resize a datafile: ( To increase or decrease the datafile)

Create a big tablespace;

Change default tablespace:

Every database will have one default tablespace. If any user is created without mentioning any tablespace_name, then that schema objects will be created under default tablespace. – Get the current default tablespace:

Rename a tablespace:

NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.

Drop a tablespace:

Make tablespace Read only:

 Take tablespace offline:

DBAs usually make the tablespace offline, while restoring or recovering any underlying corrupt datafile.

Create a temporary tablespace

Adding a tempfile:

Resize tempfile:

Drop tempfile:

It will drop and remove the tempfile from physical location.

 Change default temporary tablespace:

When we create a new user, we assign a default temporary tablespace. If nothing is assigned, then it will use the default temporary tablespace set a database level.
Get the current default temporary tablespace for the database