Introduction to Oracle Tablespace

ORACLE TABLESPACE



oracle Tablespace is logical entity which makes bridge between physical and logical components of the Oracle database.
TABLESPACE IS CREATED TO RESERVE PHYSICAL STORAGE SPACE OF ANY STORAGE MEDIA FOR THE CURRENT DATABASE. 




Find the out the storage structure of oracle database where you can see that logical component tablespace which managed different datafiles.

A tablespace is made from one or more datafile. they are not visible in the OS’s file system that's why we are calling  it as logical component. it has at least one datafile that is used to store data for the associated tablespace.

Types of Tablespace(by type)

  • 1 Permanent Tablespace
  • 2 Temporary Tablespace
  • 3 Undo Tablespace

Types of Tablespace(by size)

  • 1 Small file Tablespace
  • 2 Big File Tablespace

Oracle also creates some default tablespace while creating database which are 
  1. SYSTEM TABLESPACE
  2. SYSAUX TABLESPACE
  3. TEMPORARY TABLESPACE
  4. UNDO TABLESPACE
  5. UNDO TABLESPACE
Which are managed in "oradata" folder in oracle installed directory by default but you can change the location.Small file tablespace can have more then one datafile up to depends on your hard-disk but on other side in Big file tablespace you can't add more than one datafile in tablespace. big file tablespace's max size in 128 TB.oracle stores schema objects such as tables, indexes, Views etc. in associate tablespace. Tablespace have at least one datafile on .

1 TABLESPACE = MULTIPLE SEGMENTS (OBJECTS) 
1 SEGMENT = MULTIPLE CONTIGUOUS OR NON-CONTIGUOUS EXTENTS 
1 EXTENT = MULTIPLE CONTIGUOUS ORACLE BLOCKS 
1 ORACLE BLOCK = OS MULTIPLE BLOCKS 
1 OS BLOCK = 1024 BYTES IN WINDOWS 
1 OS BLOCK = 512 BYTES IN LINUX

A tablespace has two states 
Online (accessible) or Offline (not accessible). 

  • system tablespace stores data dictionary tables and is always online when the db is open.
  • sysaux tablespace stores data of db components like enterprise manager, recovery manager, scheduler. sysaux tablespace ought to be online for correct functioning of any of the db components. 
  • Temporary tablespace is useful when user fires SQL statement which requires temporary segments to sort large no. of data or for creation of index
So,here is the introduction of Oracle Tablespace. hope you found all the information which you need to know about tablespace.

For creating & Managing tablespace follow through my another blog 




Comments

Post a Comment

Test