1.TABLESPACE AND DATAFILES

Next ❯

1. 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.

2. TYPES OF TABLESPACE:

SYSTEM 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.
SYSAUX TABLESPACE:
  • All the database metadata, that doesnt store in SYSTEM tablespace, will be stored here.
  • Many database components datas are stored in this tablespace
PERMANENT 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.
UNDO TABLESPACE:
  • 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.
TEMPORARY 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 bestored in temp tablespace.
  • Each database will have one default temporary tablespace
Next ❯