TABLESPACE IN ORACLE.

TABLESPACES IN ORACLE

What is tablespaces:-

1. Database stores data logically in tablespaces and physically in datafiles associated with realted tablespaces.
2. Database have atleast two logical storage units called tablespace that is system and sysaux.

                

Types of Tablespaces:

• Permanent - uses data files and normally contains the system (data dictionary) and users data
• Temporary - is used to store objects for the duration of a users session, temp files are used to create temporary tablespaces
• Undo - is a permanent type of tablespace that are used to store undo data which if required would undo changes of data by users
• Read only - is a permanent tablespace that can only be read, no writes can take place, but the tablespace can be made read/write.

Permanent Tablespaces have two types

•       Bigfile tablespace and small-File tablespaces.

Small-Files tablespace have two types

• System and non-system

Differnce between Bigfile and small-file Tablespaces.

  

BIGFILE  TABLESPACES	SMALL-FILE TABLESPACES
A bigfile tablespace can contain upto 4 billion(232) blocks	A smallfile tablespace can contain upto 2 million(222) blocks.
A bigifle tablespace contains only one datafile or tempfile	A smallfile tablespace can contain upto 1022 datafiles or tempfiles
A bigfile tablespace have upto 128 TB	A small file tablespace have upto 128 GB of a single file

To create a tablespace two ways:-

Omf-(Oracle Managed Files)

Allocating space manual

OMF:-

Oracle will manage datafiles,logfile,controlfiles automatically,Resize,file craete etc.

alter system set DB_CREATE_FILE_DEST ='/home/oracle/omf_files/' scope=spfile;

alter system set DB_CREATE_ONLINE_LOG_DEST_1='/home/oracle/omf_files/' scope=spfile;

This is for Controlfiles Parameter,but it ill set to create Database first time set in pfile,DB_RECOVERY_FILE_DEST .

Allocating Space manual.

In this method we create tablesapce manually,if any space needed of relevent tablespace,we can add datafile,resize etc.

EXTENT MANAGEMENT:-

LMT(Locally Managed Tablespace)	DMT (Dictionary Managed Tablespace)
It maintains free and used in datafile header o's and 1's.	If maintain free and used in sys.FET$(free) table and sys.UET$(used).
Lmt automaticaly trac adjacent free space.	Manual colleasc the adjacent free space.
Less i/o.	More i/o.

Advantages of Locally Managed Tablespaces(LMT).

1. Reduced recursive space management

2. Reduced contention on data dictionary tables

3. No rollback generated

4. No coalescing required

Convert between LMT and DMT:

The DBMS_SPACE_ADMIN package allows dbas covert LMT to DMT.

exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');

The SYSTEM Tablespace:-(To create this tablespace during database create time)

1.    Every Oracle database contains a tablespace named SYSTEM,it automatic created when         database created.
2.    database with a locally managed SYSTEM tablespace, dictionary managed tablespaces  cannot be created.
3.    Database tables and pl/sql program units is stored.

The SYSAUX Tablespace:-

1. The SYSAUX Tablespace:-(to create this tablespace during database create time)
2. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace.
3. Many database components use the SYSAUX tablespace as their default location to store data. 
4. Not support transport tablespace.

The UNDO Tablespaces:-

1. The Undo tablespace is used for automatic undo management. early version no need unod.is uses system undo.
2. Note to create undo tablespace must add undo class
3. Used to store undo segments.
4. Cannot contain any other objects data's.
5. Extetn are locally managed.
6. Can we use only one undo tablespace in a database to set undo_tablesapce parameter.
7. you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions.

UNDO Prameters:-

      undo_tablespace=undo1

      UNDO_MANAGEMENT =Auto /Manual.

      UNDO_RETENTION = 900 s (default)

Undo status:-

   Active 

   unexpired

   expired

   

CREATE UNDO TABLEAPCE undo_tbs DATAFILE ‘/u02/oradata/grid/undo_tbs01.dbf’SIZE 1G
RETENTION GUARANTEE;

create undo tablespace undo1 datafile '/home/oracle/icici/oradata/data/undo01.dbf' size 100m;

  

  

The TEMPORARY Tablespace:-

A TEMPORARY tablespace is used to manage space for sort operations. 

 Sort operations generate segments,Large sots depents os segment   select statements with where clause. 

  -Tempfiles are also in a NOLOGGING mode. 

  -Tempfiles cannot be made read only or be renamed. 

  -Tempfiles are required for read-only databases. 

  -Tempfiles are not recovered during database recovery operations.

 Database need defualt Temporary tablesapce 

CREATE TEMPORARY TABLESAPCE TEMP TEMPFILE '/home/oracle/icici/oradata/data/temp01.dbf' size 100m;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Temporary Tablespace Groups:-

          Database default work only one temp tablespace,if you want to work more than one tablespace go temp group.

  ALTER TABLESPACE temp01 TABLESPACE GROUP tempgrp;

  ALTER TABLESPACE temp02 TABLESPACE GROUP tempgrp;

          ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;

  

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

To create a tablespace:-

create tablespace tbs1 datafile '/home/oracle/tbs1.dbf' size 1m;

create tablespace tbs2 datafile '/home/oracle/tbs2.dbf' size 1m;

Add a datafile to a tablespace:-

alter tablespace tbs1 add datafile '/home/oracle/icici/oradata/data/tabs2b.dbf' size 100m;

Resize a datafile :-

alter database  datafile '/home/oracle/tbs2.dbf' resize 100m;

alter database  datafile 5 resize 100m;

Rename Tablespace:-

ALTER TABLESPACE ts_current_name RENAME TO ts_new_name;

Drop a tablespace:-

drop tablespace tbs<tbs name>; this is no data of tablespace.

drop tablespace tbs<tbsname> including contents and datafiles;

Offline and online tablespace and datafiles:

alter tablespece  tbs<tbsname> offline;

ALTER TABLESPACE tablespace

{ONLINE |OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}

Datafiles:-

alter database datafile '/path/' offline drop;

Rename a datafile:

1.Offline a tablespace

 alter tablespace tbs offline

2.Go to os level cp datafile to any other locally

  cp /home/oracle/tbs1.dbf /home/oracle/tbs3.dbf

3.Back to database

  alter tablespace tbs rename datafile '/home/oracle/tbs1.dbf' to /home/oracle/tbs3.dbf';

4.Recover the datafile 

   Recover datafile <file_id>;

5.Bring back to tablespace online.

Alter tablespace tbs online;

Set Default Tablespace to Daatabase:-

ALTER DATABASE DEFAULT TABLESPACE users;

Rename Tablespace:-

ALTER TABLESPACE ts_current_name RENAME TO ts_new_name;

To assing a tablespace to user's.

create user d1 identified by d1 default tablespace tbs1;

alter user d1 default tablespace tbs2;

TABLESPACES VIEWS

Desc v$tablespace

desc v$datafile

desc dba_data_files

desc dba_tablespaces

desc v$datafile_header

desc dba_extents

desc DBA_TEMP_FILES

desc V$TEMPFILE

desc DBA_TEMP_FREE_SPACE

desc  V$FILESTAT