Let us we discuss about what is tablespace? and it's types?  How to create it? Mandatory tablespace in database Image taken from Oracle Documentation

tablespace in oracle

(Note: Here tablespace is logical ,datafile is physical)  

tablespace flow

Tablespace: Tablespace is logical storage unit in oracle database which consists of one or more datafiles it can't be visible in the data file system    

Tablespace Files :

tablespace types

Tablespace has different types in above picture i.e

1)system

2)sysaux

3)temp

4)undo

5)users    

i)System tablespace:

-It is mandatory  tablespace -It holds the information about all the data dictionary tables in database and  it is created at the time of database creation -We can't offline these tablespace -It's very important tablespace -It 's always online when the database is open  

ii)Sysaux tablespace

- The sysaux tablespace is system tablespace and auxiliary tablespace

-It is a new feature tablespace from 1og onwards -Half the size of system tablespace

-Snap shots -It helps on AWR report  

iii)Temp Tablespace

-It used for database sorting operations -It store temp files

iv)Undo Tablespace

-Transaction recovery purpose -It has committed and uncommitted data

v)Users

-It store information for all objects created by nonsystem users -User tablespace is to store permanent user objects and data.    

Features from 10g

1)Sysaux tablespace

2)We can rename tablespace

3)Temp group

4)Bigfile tablespace  

Data blocks:

-It is  smallest logical unit to store Oracle Data which means to represent specific number of bytes on physical hard disk

-default block is db_block_size=8k

Extends:

-It is a collection of contiguous data blocks

Segments:

-It's a  set of extents allocated for specific data structure (like table or index).

-Various kind of segments are table, index, cluster, rollback, temporary

-Important views for segments are

dba_segments,

user_segments,

all_segments  

Extend Managements

tablespace extends

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         DICTIONARY USER
SYS_UNDOTS                     LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM

   Dictionary Managed Tablespace(DMT):

-Oracle  allocates space segment (like table or index),free extents are stored in the dictionary are called dictionary managed tablespace

SQL> CREATE TABLESPACE ts1 DATAFILE 'u01/app/oracle/product/11.2.0.3/oradata/vip/ts1_01.dbf' SIZE 50M 
      EXTENT MANAGEMENT DICTIONARY 
      DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablepsace(LMT):

-Oracle  allocates space segment (like table or index),free extents are stored in the tablespace header  are called Locally managed tablespace

SQL> CREATE TABLESPACE ts2 DATAFILE 'u01/app/oracle/product/11.2.0.3/oradata/vip/ts2_01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL> CREATE TABLESPACE ts3 DATAFILE 'u01/app/oracle/product/11.2.0.3/oradata/vip/ts3_01.dbf' SIZE 50M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Auto allocated and Uniform:

-Auto allocate specifies the extend size are system managed  .optimal next extend started at 64kb  and it ll grow 1MB, 8MB, and eventually to 64MB.

-mostly Recommended for low or unmanaged environment

- Uniform specifies the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M  

Tablespace Creation syntax:

SQL> CREATE [TEMPORARY / UNDO] TABLESPACE  <tblspc_name>
 DATAFILE / TEMPFILE       '<datafile01_name and Path where file to create>' SIZE <integer M>[,
                           '<datafile02_name and Path where file to create>' SIZE <integer M>[,
                           '<datafile0N_name and Path where file to create>' SIZE <integer M>[,...]]]
 BLOCKSIZE  <DB_BLOCK_SIZE parameter /2k/4k/8k/16k/32k >
 AUTOEXTEND { [OFF/ON (NEXT <integer K/M >  MAXSIZE<integer K/M >) / UNLIMITED] } 
 LOGGING/NOLOGGING (Logging default) 
 ONLINE/OFFLINE (Online default)
 EXTENT MANAGEMENT { [DICTIONARY] /
                     [LOCAL Default (AUTOALLOCATE / UNIFORM SIZE <integer K/M >)] }
 PERMANENT  / TEMPORARY (Permanent default)
 MINIMUM EXTENT
 DEFAULT STORAGE  {    [INITIAL <integer K/M >]
                       [NEXT <integer K/M >]
                       [PCTINCREASE <integer K/M >]
                       [MINEXTENTS <integer>]
                       [MAXEXTENTS <integer> / UNLIMITED]
                       [FREELISTS <integer>]
                       [FREELIST GROUPS <integer>]
                       [OPTIMAL <integer>/NULL]
                       [BUFFER_POOL < DEFAULT/KEEP/RECYCLE >] }
 CHUNK <integer K/M >
 NOCACHE;

*BLOCKSIZE - By default block size is defined by this parameter DB_BLOCK_SIZE.In oracle 9i multiple blocksize that is different block size for different tablespaces, can be defined, all datafiles of a same tablespace have the same block size.

*DEFAULT STORAGE :

-INITIAL – Specifies the size of the object's first extent.3 k minmum for Locally and 2 k minimum Dictionary.

-NEXT – Specifies the size of the object's sucessive extent.

-PCTINCREASE – Specifies the ratio of the third or the preceding extent of the object. The default value for PCTINCREASE is 50 % and the minimum value is 0%.

-MINEXTENTS – The total number of extent allocated to the segment at the time of creation

-MAXEXTENTS – The maximum number of extent that can be allocated to the segment .

*MININUM EXTENT – The size is specifies in this clause.The extent are multiple of the size specified in this clause .NEXT and INITIAL extent size specified should be multiple of minmum extent.

*PERMANENT / TEMPORARY – Permanent is default, use to store the table,index etc,Temporary is for temporay segments(sorts in Sql) can not store table,index in temporary tablespace.

*LOGGING / NOLOGGING – Logging is default,the DDL operation & direct insert load are recorded in the redo log file.

*ONLINE / OFFLINE - Online is default,tablespace is available as soon as created.      

Bigfile Tablespace:

-It's special kind of tablespace which was introduced on oracle 10g and it have a single datafile.

-Bigfile tablespaces can be up to 4G blocks that 128 TB with 32k blocks

-We can resize the tablespace and datafiles ,but we can't add new datafiles  

SQL> create bigfile tablespace Don datafile '/u01/app/oracle/product/11.2.0.3/oradata/vip/don.dbf' size 20m;

Resize:

SQL> alter tablespace don resize 20m;

Rename:

SQL> alter tablespace don rename to sam;

Smallfile Tablespace:

-It are restricted datafiles -We can't resize tablespace size but we can do  datafile size

SQL> create tablesspace sam datafile '/u01/app/oracle/product/11.2.0.3/oradata/vip/sam.dbf' size 10m;

Adding Datafiles:

SQL> alter tablespace sam add datafile '/u01/app/oracle/product/11.2.0.3/oradata/vip/sam02.dbf' size 30m;

Resize Existing Datafiles:

SQL> alter database datafile '/u01/app/oracle/product/11.2.0.3/oradata/vip/sam02.dbf' resize 35m;

Drop Tablespace:

Drop Tablespace is remove tablespace from oracle

SQL> DROP TABLESPACE tablespace_name
  [ INCLUDING CONTENTS [ {AND DATAFILES | KEEP DATAFILES ]
    [ CASCADE CONSTRAINTS ] ] ;

Here

INCLUDING CONTENTS - If you specify INCLUDING CONTENTS, all contents of the tablespace will be dropped. If there are objects in the tablespace, you must specify INCLUDING CONTENT or you will receive an error.

DATAFILES - It ll delete the associate os files

KEEP DATAFILES - The Os file did not deleted it ll be available

CASACADE CONSTRAINTS - all referential integrity constraints will be dropped  

(Note:  When you are going to drop tablespace before  take that tablespace into offline its recommended method by oracle)