TABLE SPACE MANAGEMENT

A small database might need only the SYSTEM tablespace; however, Oracle recommends that
you create additional tablespaces to store user data, user indexes, undo segments, and
temporary segments separate from data dictionary.

The DBA can create new tablespaces, resize data files, add data files to tablespaces, set and
alter default segment storage settings for segments created in a tablespace, make a tablespace
read-only or read-write, make a tablespace temporary or permanent, and drop

tablespaces.

• The data in an Oracle database are stored in tablespaces.
• An Oracle database can be logically grouped into smaller logical areas of space known
  as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.

Table spaces are two types

1)system tablespace 2)nonsystem tablespace

SYSTEM tablespace

*Created with the database
*Contains the data dictionary
*Contains the SYSTEM undo segment

Non-SYSTEM tablespaces

*Separate segments
*Ease space administration
*Control amount of space allocated to a user

Space Management in Tablespaces

Table spaces are managed by two types

1)locally managed 2)Dictionary managed

locally managed:-The extents are managed with in the tablespace via bitmaps

*Free extents recorded in bitmap
*Each bit corresponds to a block or group of blocks
*Bit value indicates free or used

Dictionary managed:-the extents are managed by the data dictionary segments in dictionary managed tablespace can have customized storage.

*Free extents recorded in data dictionary tables

syntax for creation of tablespaces

SQL>create tablespace svs datafile '$ORACLE_HOME/oradata/svs.dbf ' size 10m extent management local;

SQL>create tablespace svs1 datafile '$ORACLE_HOME/oradata/svs1.dbf' size 20m extent management local autoallocated;

SQL>create tablespace svs2 datafile '$ORACLE_HOME/oradata/svs2.dbf ' size 30m extent management local uniform size 10k;

SQL>create tablespace svs3 datafile '$ORACLE_HOME/oradata/svs3.dbf ' size 10m extent management dictionary;

SQL>create tablespace svs4 datafile '$ORACLE_HOME/oradata/svs4.dbf ' size 10m extent management dictionary default storage (initial 1m next 1m pct increase0);

we can give storage parameters in three levels

1)server level

2)tablespace level

3)segment level