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

Post a Comment

Popular posts from this blog

What is Oracle Integration Cloud Service - ICS?

Image
Oracle Integration Cloud Service (ICS) is a solution that enables you to simplify integrations between cloud applications, and between cloud and on premises applications. It helps you create connections to well-known and less-known SaaS and PaaS applications, using the available cloud adapters, publish or subscribe to the Messaging Cloud Service, or use industry standards such as SOAP, REST, FTP, File, and JMS. For more information watch this Oracle video.
Read more

How to Create Packages in Oracle Database using TOAD for Oracle

Image
What are Packages in Oracle Database A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification and Package Body. Package Specification Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Package Body Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification.
Read more

How to create a Simple Scheduler Job in Oracle Database using Toad

Image
Scheduler is a process which runs programmed jobs at scheduled times. This process wakes up at the specified time and runs automatically. The basic elements of the scheduler are: Jobs Schedules Programs A job specifies what needs to be executed and when. A program is a collection of metadata that will be executed by the scheduler. It contains the name of the program (a procedure or an executable, the type of the program (pl/sql block, shell script etc). A schedule specifies when and how many times a job is executed. 1. Login to the database using Toad, and create a Scheduler Job as follows;
Read more