Oracle Database 11g - Tablespace



TABLESPACE

A tablespace is where Oracle allocates space for your database. Without this you cannot create tables or store data.

CREATE TABLESPACE
DATAFILE datafile SIZE [ AUTOEXTEND ON NEXT size [M] MAXSIZE size [M]]

For example:

SQL> CREATE TABLESPACE test
DATAFILE ‘/opt/oracle/oradata/SID/test.dbf’ SIZE 10M;

SQL> CREATE TABLESPACE test
DATAFILE ‘/opt/oracle/oradata/SID/test01.dbf’ SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;


BIGFILE TABLESPACES:

A bigfile Tablespaces is new to Oracle since Oracle 10g – a single datafile is allocated and it can be up to 8EB (Exabyte, a million terabytes) in size.

Why are these important?

• The maximum number of datafiles in an Oracle Database is limited (usually to 64K files) – think big here – think about a database for the Internal Revenue Service or NADRA (these are the Government Departments of Pakistan contain huge amount of data).

• A Bigfile Tablespace with 8K blocks can contain a 32 terabyte datafile.

• A Bigfile Tablespace with 32K blocks can contain a 128 terabyte datafile.

• These sizes enhance the storage capacity of an Oracle Database.

• These sizes can also reduce the number of datafiles to be managed.

Bigfile Tablespaces can only be locally managed with automatic segment space management except for locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.

If a Bigfile Tablespace is used for automic undo or temporary segments, the segment space management must be set to MANUAL.

Bigfile Tablespaces save space in the SGA and control file because fewer datafiles need to be tracked.

ALTER TABLESPACE commands on a Bigfile Tablespace do not reference a datafile because only one datafile is associated with each bigfile tablespace.

SQL> CREATE BIGFILE TABLESPACE pim01 DATAFILE ‘/opt/oracle/oradata/pim01.dbf’ SIZE 10g;

SQL> ALTER TABLESPACE pim01 resize 40g;

SQL> ALTER TABLESPACE pim01 AUTOEXTEND ON NEXT 10g;
Tablespace altered.

Read Only Tablespaces:
A tablespace may be made read only. One purpose of this action is to enable system maintenance that involves dropping tables and associated indexes stored in the tablespace. This can be accomplished while a tablespace is in read only mode because the DROP command affects only information in the Data Dictionary which is in the SYSTEM tablespace, and the SYSTEM tablespace is not read only.

SQL> ALTER TABLESPACE pim01 READ ONLY;

To change a tablespace from read only to writable, all datafiles for the tablespace must be online.

SQL> ALTER TABLESPACE pim01 READ WRITE;

Offline Tablespaces:
Most tablespaces are online all of the time; however, a DBA can take a tablespace offline. This enables part of the database to be available – the tablespaces that are online – while enabling maintenance on the offline tablespace.

• Offline tablespace backup – a tablespace can be backed up while online, but offline backup is faster.

• Recover an individual tablespace or datafile.

• Move a datafile without closing the database.

You can not use SQL to reference offline tablespace – this simply generate error.

Additionally, the action of taking a tablespace offline/online is always recorded in the Data Dictionary and Control files. Tablespaces that are offline when you shutdown a database are offline when the database is again opened.

SQL> ALTER TABLESPACE pim01 OFFLINE;

SQL> ALTER TABLESPACE pim01 ONLINE;

The full syntax is:

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

NORMAL:
All data blocks for all datafiles that from the tablespace are written from the SGA to the datafiles. A tablespace that is offline NORMAL dose not require any type of recovery when it is brought back online.

TEMPORARY:
A checkpoint is performed for all datafiles in the tablespace. Any offline may require media recovery.

IMMEDIATE:
A checkpoint is NOT performed. Media recovery on the tablespace is required before it is brought back online to synchronize the database objects.

FOR RECOVER:

Used to place a tablespace in offline status to enable point-in-time recovery.

Comments

Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle