Tablespace Management

A Tablespace is a

• Logical storage unit
• Bridge between objects and physical files
• Collection of Physical files ( Eg : System.dbf, Sysaux.dbf, UNDO.dbf, userdata.dbf )

 Oracle contains 3 types of tablespaces, which is an allocation of space in the database that can contain schema objects.

• A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
• An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
• A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.

Prerequisites to create a Tablespace:

• You must have the CREATE TABLESPACE system privilege 
• To create the SYSAUX tablespace, you must have the SYSDBA system privilege.

Syntax: 

SQL>  CREATE TABLESPACE <TBS>  DATAFILE <'/DISK2/ORADATA/RAC18/DATA.dbf'> SIZE 10M;

SQL> DROP TABLESPACE USERS;
With above statement, Tablespace will be dropped logically. Physically the datafiles will not be dropped. It just disconnects the link.
If we want to drop contents and datafiles, then we need to fire below query
SQL> DROP TABLESPACE USERS INCLUDING  CONTENTS AND DATAFILES;

BIGFILE | SMALLFILE

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (222) blocks.

SQL>  CREATE BIGFILE  TABLESPACE <TBS>  DATAFILE <'/DISK2/ORADATA/RAC18/DATA.dbf'> SIZE 10M;

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.

Uses of Tablespaces:

• Availability
• Performance

Working with Tablespaces:

• We can have 65536 tablespaces in database
• We can see the details of tablespaces in DBA_TABLESPACES
  SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
• Status of a tablespace can be Off line or On line ( READ ONLY or READ WRITE )
  SQL> ALTER TABLESPACE USERS READ ONLY;
  SQL> ALTER TABLESPACE USERS READ WRITE;
  SQL> ALTER TABLESPACE USERS OFFLINE;
• We can see the details of datafiles in DBA_DATA_FILES
  SQL> SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024  FROM DBA_DATA_FILES;
• To reuse a physical file
  SQL> CREATE TABLESPACE TBS DATAFILE  '</loc> ' REUSE;

Resizing the tablespace:

• SQL> ALTER TABLESPACE USERS ADD DATAFILE </loc> SIZE 20M;
• SQL> ALTER TABLESPACE USERS DROP DATAFILE </loc> ;
• SQL> ALTER DATABASE DATAFILE 4 RESIZE 40M;
  OR
  SQL> ALTER DATABASE DATAFILE  </loc>  RESIZE 40M;
• SQL> ALTER DATABSE DATAFILE 4 AUTO EXTEND ON;

Temporary Tablespaces:

It is allocation of space in the database that can contain schema objects for the duration of a session.