Oracle Tablespaces

 Oracle Tablespaces

In Oracle Database, a tablespace is a logical storage container that groups together one or more data files, which are physical files on disk that store database objects such as tables, indexes, and other data structures. Tablespaces provide a way to organize and manage the storage of data within an Oracle database.

Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. 

Figure 3-1 Datafiles and Tablespaces

Databases, tablespaces, and datafiles are closely related, but they have important differences:

·        An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.

·        Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

·        A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

system interfaces to create and delete files as needed for the following database structures:

§  Tablespaces

§  Online redo log files

§  Control files

Through initialization parameters, you specify the file system directory to be used for a particular type of file. Oracle then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

You specify operations in terms of database objects rather than filenames. Oracle internally uses standard file.

Allocate More Space for a Database

The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.

You can enlarge a database in three ways:

·        Add a datafile to a tablespace

·        Add a new tablespace

·        Increase the size of a datafile

 

When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace. 

Figure 3-2 Enlarging a Database by Adding a Datafile to a Tablespace.

Alternatively, you can create a new tablespace (which contains at least one additional datafile) to increase the size of a database. 

Figure 3-3 Enlarging a Database by Adding a New Tablespace. 

The third option for enlarging a database is to change a datafile's size or let datafiles in existing tablespaces grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension properties. 

Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles. 

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

The following tablespaces available in Oracle:

·        The SYSTEM Tablespace

·        Undo Tablespaces

·        Default Temporary Tablespace

·        Using Multiple Tablespaces

·        Managing Space in Tablespaces

·        Multiple Block Sizes

·        Online and Offline Tablespaces

·        Read-Only Tablespaces

·        Temporary Tablespaces for Sort Operations

·        Transport of Tablespaces Between Databases

 

The SYSTEM Tablespace

Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

 

To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.

 

In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.

 

Note:

Once a tablespace is locally managed, it cannot be reverted back to being dictionary managed

The Data Dictionary

 

The SYSTEM tablespace always contains the data dictionary tables for the entire database. The data dictionary tables are stored in datafile 1.

 

PL/SQL Program Units Description

All data stored on behalf of stored PL/SQL program units (that is, procedures, functions, packages, and triggers) resides in the SYSTEM tablespace. If the database contains many of these program units, then the database administrator must provide the space the units need in the SYSTEM tablespace.

 

Undo Tablespaces

Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.

When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. In rare circumstances, if the instance does not have a designated undo tablespace, the transaction binds to the system undo segment.

Caution: Do not run any user transactions before creating the first undo tablespace and taking it online.

Each undo tablespace is composed of a set of undo files and is locally managed. Like other types of tablespaces, undo blocks are grouped in extents and the status of each extent is represented in the bitmap. At any point in time, an extent is either allocated to (and used by) a transaction table, or it is free.

Creation of Undo Tablespaces

A database administrator creates undo tablespaces individually, using the CREATE UNDO TABLESPACE statement. It can also be created when the database is created, using the CREATE DATABASE statement. A set of files is assigned to each newly created undo tablespace. Like regular tablespaces, attributes of undo tablespaces can be modified with the ALTER TABLESPACE statement and dropped with the DROP TABLESPACE statement.

CREATE UNDO TABLESPACE undo_tbs

DATAFILE '/path/to/undo_tbs01.dbf' SIZE 500M

 AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

After creating the undo tablespace, you may need to set it as the default undo tablespace for your database. This can be done using the ALTER DATABASE statement:

ALTER DATABASE DEFAULT UNDO TABLESPACE undo_tbs;

Note: An undo tablespace cannot be dropped if it is being used by any instance or contains any undo information needed to recover transactions.

Assignment of Undo Tablespaces

You assign an undo tablespace to an instance in one of two ways:

·        At instance startup. You can specify the undo tablespace in the initialization file or let the system choose an available undo tablespace.

·        While the instance is running. Use ALTER SYSTEM SET UNDO_TABLESPACE to replace the active undo tablespace with another undo tablespace. This method is rarely used.

You can add more space to an undo tablespace by adding more data files to the undo tablespace with the ALTER TABLESPACE statement.

You can have more than one undo tablespace and switch between them. Use the Database Resource Manager to establish user quotas for undo tablespaces. You can specify the retention period for undo information.

Default Temporary Tablespace

When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.

How to Specify a Default Temporary Tablespace

Specify a default temporary tablespace when you create a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement.

If you drop the default temporary tablespace, then the SYSTEM tablespace is used as the default temporary tablespace.

 

Note:

You cannot make the default temporary tablespace permanent or take it offline.

 

During User Creation:

When creating a new user, you can specify the default temporary tablespace using the TEMPORARY TABLESPACE clause:

CREATE USER your_username IDENTIFIED BY your_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp_ts;

In this example, temp_ts is the name of the temporary tablespace. Replace your_username, your_password, and temp_ts with your actual values.

Altering an Existing User:

If the user has already been created, you can use the ALTER USER statement to modify the default temporary tablespace:

ALTER USER your_username TEMPORARY TABLESPACE temp_ts;

Replace your_username and temp_ts with your actual values.

Verify Default Temporary Tablespace:

To verify the default temporary tablespace for a user, you can query the DBA_USERS view:

SELECT username, temporary_tablespace FROM dba_users WHERE username = 'your_username';

Using Multiple Tablespaces

A very small database may need only the SYSTEM tablespace; however, Oracle Corporation recommends that you create at least one additional tablespace to store user data separate from data dictionary information. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles.

You can use multiple tablespaces to perform the following tasks:

·        Control disk space allocation for database data

·        Assign specific space quotas for database users

·        Control availability of data by taking individual tablespaces online or offline

·        Perform partial database backup or recovery operations

·        Allocate data storage across devices to improve performance

A database administrator can use tablespaces to do the following actions:

·        Create new tablespaces

               CREATE TABLESPACE your_tablespace

               DATAFILE '/path/to/your_tablespace01.dbf' SIZE 100M

               AUTOEXTEND ON NEXT 10M MAXSIZE 1G

               ONLINE;

·        Add datafiles to tablespaces

ALTER TABLESPACE your_tablespace

ADD DATAFILE '/path/to/your_additional_datafile.dbf' SIZE 200M

AUTOEXTEND ON NEXT 20M MAXSIZE 1G;

·        Set and alter default segment storage settings for segments created in a tablespace

Set Default Segment Storage Settings:

 

               ALTER TABLESPACE your_tablespace

               DEFAULT STORAGE (

               INITIAL 64K

               NEXT 64K

               MINEXTENTS 1

               MAXEXTENTS UNLIMITED

               PCTINCREASE 0

               FREELIST GROUPS 1

               );

              

   Alter Default Segment Storage Settings:

               ALTER TABLESPACE your_tablespace

DEFAULT STORAGE (

    INITIAL 128K

    NEXT 128K

    MINEXTENTS 2

    MAXEXTENTS 100

    PCTINCREASE 50

    FREELIST GROUPS 2

);

·        Make a tablespace read-only or read/write

               ALTER TABLESPACE your_tablespace READ ONLY;

               ALTER TABLESPACE your_tablespace READ WRITE;

SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = 'your_tablespace';

·        Make a tablespace temporary or permanent

Make a Tablespace Temporary:

ALTER TABLESPACE your_tablespace TEMPFILE '/path/to/tempfile.dbf' REUSE;

Make a Tablespace Permanent:

Make a Tablespace Permanent:

If you want to convert a temporary tablespace back to a permanent tablespace, you can use the ALTER TABLESPACE statement with the DROP TEMPFILE clause:

 

ALTER TABLESPACE your_temporary_tablespace DROP TEMPFILE '/path/to/tempfile.dbf';

Replace 'your_temporary_tablespace' with the name of the temporary tablespace, and '/path/to/tempfile.dbf' with the path to the temporary data file.

After dropping the temporary file, you can add a new data file to make the tablespace permanent again:

 

ALTER TABLESPACE your_temporary_tablespace ADD DATAFILE '/path/to/new_datafile.dbf' SIZE 100M;

Replace 'your_temporary_tablespace' with the name of the temporary tablespace and '/path/to/new_datafile.dbf' with the path to the new data file.

 

·        Drop tablespaces.

               DROP TABLESPACE your_tablespace INCLUDING CONTENTS AND DATAFILES;

               Important Points to Note:

1.      Data Backup: Before dropping a tablespace, ensure that you have a valid backup of the data. Dropping a tablespace is irreversible, and you will lose all data within it.

2.      Tablespace State: Make sure that the tablespace is in a READ WRITE state before attempting to drop it. If the tablespace is in READ ONLY mode or has offline data files, you may need to bring it online or make it READ WRITE before dropping.

3.      Tablespace Quiesce: If there are active transactions or sessions using the tablespace, you may need to quiesce or take the tablespace offline before attempting to drop it.

4.      Privileges: Ensure that you have the necessary privileges to drop tablespaces. Typically, you need the DROP TABLESPACE or DROP ANY TABLESPACE system privilege.

Example with Quiesce and Offline:

-- Quiesce the tablespace to ensure no active transactions

ALTER TABLESPACE your_tablespace QUISCESCE;

-- Optionally, take the tablespace offline if needed

ALTER TABLESPACE your_tablespace OFFLINE;

-- Drop the tablespace and its contents and data files

DROP TABLESPACE your_tablespace INCLUDING CONTENTS AND DATAFILES;

Managing Space in Tablespaces

Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:

·        Locally managed tablespaces: Extent management by the tablespace

·        Dictionary managed tablespaces: Extent management by the data dictionary

When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time.

Note:

If you do not specify extent management when you create a tablespace, then the default is locally managed

 

Online and Offline Tablespaces

A database administrator can bring any tablespace other than the SYSTEM tablespace online (accessible) or offline (not accessible) whenever the database is open. The SYSTEM tablespace is always online when the database is open because the data dictionary must always be available to Oracle.

A tablespace is usually online so that the data contained within it is available to database users. However, the database administrator can take a tablespace offline for maintenance or backup and recovery purposes.

Use of Tablespaces for Special Procedures

If you create multiple tablespaces to separate different types of data, you take specific tablespaces offline for various procedures. Other tablespaces remain online, and the information in them is still available for use. However, special circumstances can occur when tablespaces are taken offline. For example, if two tablespaces are used to separate table data from index data, the following is true:

·        If the tablespace containing the indexes is offline, then queries can still access table data because queries do not require an index to access the table data.

·        If the tablespace containing the tables is offline, then the table data in the database is not accessible because the tables are required to access the data.

If Oracle has enough information in the online tablespaces to run a statement, it does so. If it needs data in an offline tablespace, then it causes the statement to fail.

Read-Only Tablespaces

The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle never updates the files of a read-only tablespace, and therefore the files can reside on read-only media such as CD-ROMs or WORM drives.

Note:

Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data publishing requirements.

Read-only tablespaces cannot be modified. To update a read-only tablespace, first make the tablespace read/write. After updating the tablespace, you can then reset it to be read-only.

Because read-only tablespaces cannot be modified, and as long as they have not been made read-write at any point, they do not need repeated backup. Also, if you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.

Temporary Tablespaces for Sort Operations

You can manage space for sort operations more efficiently by designating temporary tablespaces exclusively for sorts. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space.

All operations that use sorts, including joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces. The performance gains are significant with Real Application Clusters.

 

Transport of Tablespaces Between Databases

A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another on the same platform. You can clone a tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases on the same platform.

Moving data by transporting tablespaces can be orders of magnitude faster than either export/import or unload/load of the same data, because transporting a tablespace involves only copying datafiles and integrating the tablespace metadata. When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data.

Note:

You can transport tablespaces only between Oracle databases that use the same character set and that run on compatible platforms from the same hardware vendor.

How to Move or Copy a Tablespace to Another Database

To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use export/import to move the database information (metadata) stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs.

After copying the datafiles and importing the metadata, you can optionally put the tablespaces in read/write mode.

Note:

In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.

Key points about tablespaces in Oracle:

1.    Logical Storage Unit: A tablespace is a logical storage unit within an Oracle database. It is used to store and manage segments, which are the logical storage structures for database objects.

2.    Data Files: Each tablespace is associated with one or more data files. These data files can be spread across different physical disks or file systems. The data files store the actual data and objects within the tablespace.

3.    Types of Tablespaces:

·      System Tablespace: The system tablespace contains the data dictionary and other objects needed for the operation of the database. It is created automatically when a database is created.

·      User Tablespaces: These are tablespaces created by the database administrator or users to store application data. They can be used to organize data based on different criteria such as functionality or user groups.

·      Temporary Tablespaces: Used to store temporary data generated during the execution of SQL statements. Temporary tablespaces are used for sorting and joining operations.

4.    Tablespace Management:

·      Locally Managed Tablespaces: In locally managed tablespaces, space management is handled at the tablespace level, and bitmap segments are used to manage extents.

·      Dictionary Managed Tablespaces: In dictionary managed tablespaces, space management is controlled by the data dictionary, and the free space information is stored in the data dictionary tables.

5.    Tablespace Quotas: Users are assigned quotas on tablespaces to control the amount of space they can use within a specific tablespace.

6.    Tablespace Encryption: Oracle provides the option to encrypt the data stored in tablespaces to enhance security.

7.    Tablespace Transportability: Tablespaces can be transported between databases, making it easier to move subsets of data or entire applications.

Understanding and effectively managing tablespaces is an essential aspect of Oracle Database administration, as it allows for efficient storage management and optimization of database performance.