3 - 4 minutes readManaging Tablespace

A tablespace is a logical storage unit. Why we are say logical because a tablespace is not visible in the file system. Oracle store data physically is datafiles. A tablespace consist of one or more datafile.

Type of tablespace?

System Tablespace

• Created with the database
• Required in all database
• Contain the data dictionary

Non System Tablespace:

• Separate undo, temporary, application data and application index segments Control the amount of space allocation to the user’s objects
• Enable more flexibility in database administration

How to Create Tablespace?

CREATE TABLESPACE "tablespace name"

DATAFILE clause SIZE ……. REUSE

MENIMUM EXTENT (This ensure that every used extent size in the tablespace is a multiple of the integer)

BLOCKSIZE

LOGGING | NOLOGGING (Logging: By default tablespace have all changes written to redo, Nologging : tablespace do not have all changes written to redo)

ONLINE | OFFLINE (OFFLINE: tablespace unavailable immediately after creation)

PERMANENT | TEMPORARY (Permanent: tablespace can used to hold permanent object, temporary: tablespace can used to hold temp object)

EXTENT MANAGEMENT clause

Example:

CREATE TABLESPACE "USER1"

DATAFILE ‘C:LOCALORADATAUSER_DATA.DBF’ SIZE 10m REUSE

BLOCKSIZE 8192

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL

How to manage space in Tablespace?

Tablespace allocate space in extent.

Locally managed tablespace:

The extents are managed with in the tablespace via bitmaps. In locally managed tablespace, all tablespace information store in datafile header and don’t use data dictionary table for store information. Advantage of locally managed tablespace is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.

Extent Management [Local | Dictionary]

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid for segments stored in locally managed tablespaces.

To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).

If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice.

If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Dictionary Managed tablespace

When we declaring a tablespace as a Dictionary Managed, the data dictionary manages the extents. The Oracle server updates the appropriate tables (sys.fet$ and sys.uet$) in the data dictionary whenever an extent is allocated or deallocated.

How to Create a Locally Managed Tablespace?

The following statement creates a locally managed tablespace named USERS, where AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE users

DATAFILE ‘C:LOCALORADATAUSER_DATA.DBF’ SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 512K extent size is specified. Each 512K extent (which is equivalent to 64 Oracle blocks of 8K) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE users

DATAFILE ‘C:LOCALORADATAUSER_DATA.DBF’ SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

How to Create a Dictionary Managed Tablespace?

The following is an example of creating a DICTIONARY managed tablespace in Oracle9i:

CREATE TABLESPACE users

DATAFILE ‘C:LOCALORADATAUSER_DATA.DBF’ SIZE 50M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (

INITIAL 64K

NEXT 64K

MINEXTENTS 2

MAXEXTENTS 121

PCTINCREASE 0);

What is Segment Space Management Options?

Two choices for segment-space management, one is manual (the default) and another auto.

Manual: This is default option. This option use free lists for managing free space within segments. What are free lists: Free lists are lists of data blocks that have space available for inserting new rows.

Auto: This option use bitmaps for managing free space within segments. This is typically called automatic segment-space management

Example:

CREATE TABLESPACE users

DATAFILE ‘C:LOCALORADATAUSER_DATA.DBF’ SIZE 10M REUSE

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

SEGMENT SPACE MANAGEMENT AUTO

PERMANENT

ONLINE;

How to Convert between LMT and DMT Tablespace?

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local(‘ts1’);

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local(‘ts2’);

PL/SQL procedure successfully completed.