ORACLE : Managing Tablespace

Managing Tablespace

1.Tablespace Management

Using multiple tablespaces allows you more flexibility in performing database operations. For example, when a database has multiple tablespaces, you can perform the following tasks:

Ø Separate user data from data dictionary data to reduce contention among dictionary objects and schema objects for the same datafiles

Ø Separate one application's data from another's to prevent multiple applications from being affected if a tablespace must to be taken offline

Ø Store different tablespaces' datafiles on separate disk drives to reduce I/O contention

Ø Separate rollback segment data from user data, preventing a single disk failure from causing permanent loss of data.




2. Locally Managed Tablespaces
Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps, resulting in the following benefits

Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues



Create Tablespace (Locally Managed)

Sql> CREATE TABLESPACE name DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Altering Tablespace adding datafiles to the tablespace

Sql> ALTER TABLESPACE name
ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M


3.Dictionary Managed Tablespace

Storage information of the tablespace space available and freelist will maintained in System tablespace data dictionary

Create Dictionary Managed Tablespace

Sql> CREATE TABLESPACE tbsb
DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (
INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 0);

Altering a Dictionary-Managed Tablespace
One reason for using an ALTER TABLESPACE statement is to add a datafile. The following statement creates a new datafile for the tbsa tablespace:
SQL> ALTER TABLESPACE tbsa
ADD DATAFILE '/u02/oracle/data/tbsa02.dbf' SIZE 1M;

You might also want to change the default storage parameters.
You can change the default storage parameters of a tablespace using the ALTER TABLESPACE statement, as illustrated in the following example:

SQL> ALTER TABLESPACE users
DEFAULT STORAGE (
NEXT 100K
MAXEXTENTS 20
PCTINCREASE 0);