Oracle - Tablespace Management

In this post I will guide you through the steps on creating the tablespace in Oracle 12c Database (the steps are equally applicable for 11g as well)

To create the tablespace and manage it, we need the first the Database be created (Find the detailed steps for creating a database here).

First let’s start with understanding the Tablespaces & Datafiles.

Oracle database stores the data logically in tablespaces and physically in filesystem Datafiles associated with the tablespaces. Below screenshot (courtesy: oracle docs) depicts the relation between the Tablespace (Logical Structure) to DataFiles (Physical Structure) in Oracle Database.

Few important points to be noted about Tablespaces & DataFiles

-    An Oracle Database consists of at least two tablespaces (SYSTEM & SYSAUX explained below), and optional tablespaces – TEMP & UNDOTBS(it is always good practice to have these Tablespaces as well)

-    A Tablespace consists of one or more datafiles and the datafiles are not shared across by multiple tablespaces.

-    Objects (Tables, Indexes etc.,) created in the Tablespaces can span across multiple datafile associated with a Tablespace.

Below are the Tablespaces created in my database (MyDB) and the Datafiles associated with it.

Let’s try to add a datafile to Tablespace USERS and try to add the same again to UNDOTBS, to demonstrate that one datafile can be associated with ONLY one tablespace. Trying to add the Datafile associated already to a tablespace to other Tablespace will error out – ‘ORA-01537 -  ... file already part of database’

You can create multiple no. of tablespaces and most important ones which are required to be mentioned during the database creation are

-    SYSTEM Tablespace: This is the primary tablespace, which contains information basic for functioning of the database server, such as Data Dictionary & System rollback segments.

This is the first tablespace created during the database creation. We cannot rename, drop or take offline.

-    SYSAUX (Auxiliary to SYSTEM) Tablespace: It is the default tablespace for many database features or products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace. Similar to SYSTEM Tablespace, we cannot drop or rename the SYSAUX Tablespace. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation.

-    UNDO Tablespace: Undo Tablespaces are special tablespaces used solely for storing the undo information (which is used for – database recovery, read consistency etc.,) and we cannot create any other database objects (tables, indexes etc.,) in these tablespaces. Undo Tablespaces are used only when database is in automatic undo management – Init Parameter “undo_management” (this is default mode though)

SQL> show parameter undo_management

NAME                                   TYPE                 VALUE

------------------------------------ ----------- ------------------------------

undo_management                 string                AUTO

Let’s create a new Tablespace in Database MyDB created in my Virtual Linux server.

To create/alter a tablespace, the user should have the CREATE/ALTER TABLESPACE system privileges (To know different system privileges granted to a user – query the DBA_SYS_PRIVS table. As I connected to database as SYS, the user is SYS)

Guidelines for managing Tablespaces:

-          - Create multiple tablespaces one for each application, that makes the availability of other applications when a tablespace associated with other application is made offline for maintenance activities.

-          - Store datafiles associated with different tablespaces on different disk drives, that way I/O contention on the drive is reduced

-          - Backup Individual tablespaces separately that way restore/recovery process will be simple as per our need.

-          - Assign tablespace quotas to users to hold the intended object segments.

Creating a Locally Managed Tablespace:

To create the tablespace with extents managed locally, we need to mention the clause – ‘Extent Management’. If you want the database manage the extents automatically, mention – ‘AUTOALLOCATE’ (If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes) or ‘UNIFORM’ (If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents) if you want to manage the tablespace with uniform extent size.

Below is the query to create a locally managed Tablespace with 100MB datafile size, extent management local & autoallocate.

CREATE TABLESPACE EXAMPLE DATAFILE ‘/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf' SIZE 100M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

The alternative to AUTOALLOCATE is UNIFORM, which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.

Specifying Segment Space Management in Locally Managed Tablespaces:

Segment space management clause of a create tablespace has two options – MANUAL or AUTO

MANUAL - Manual segment space management uses linked lists called "freelists" to manage free space in the segment

AUTO- Automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces

Let’s drop example1 Tbs created above and recreate the same using the CREATE TABLESPACE statement with explicitly mentioning the Segment Management as AUTO

Bigfile Tablespaces

-          - If Database is created by mentioning Bigfile as default for TBs creation, then CREATE TABLESPACE.. statement creates the tablespace as Bigfile Tablespace

-          - Bigfile tablespaces are by default EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO.

       - If you specify EXTENT MANAGEMENT DICTIONARY and SEGMENT SPACE MANAGEMENT MANUAL, then the TBs creation will error out

-                    -  A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

-                    - To find the default TBs type using which the database is created can be found by querying DATABASE_PROPERTIES table

Encrypted Tablespaces

-          - TBs encryption is applicable to Permanent TBs ONLY

-          - Any user who is granted privileges on objects stored in an encrypted tablespace can access those objects without providing any kind of additional password or key

-          - Data from an encrypted tablespace is automatically encrypted when written to the undo tablespace, to the redo logs, and to any temporary tablespace. There is no need to explicitly create encrypted undo or temporary tablespaces, and in fact, you cannot specify encryption for those tablespace types.

-          - Transparent data encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:

§  3DES168

§  AES128(default when USING keyword is not mentioned)

§  AES192

§  AES256

-          - You cannot encrypt an existing tablespace with an ALTER TABLESPACE statement. However, you can use Data Pump or SQL statements such as CREATE TABLE AS SELECT or ALTER TABLE MOVE to move existing table data into an encrypted tablespace.

-          - Encryption algorithm implemented for a TBs can be determined by querying - v$encrypted_tablespaces

-          - Tablespace encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create the encrypted tablespace and before you can store or retrieve encrypted data.

When we try to create the encrypted TBs without create/open the oracle wallet, then – ‘ORA-28365: wallet is not open’ will be thrown

To correct the above error, create a directory named – ‘wallet’ as in here $ORACLE_HOME/admin/$ORACLE_SID/wallet.

And mention the same in sqlnet.ora file as below

Shutdown/Restart the instance and open the oracle wallet using the ALTER SYSTEM… and then create the encrypted TBs

CREATE TABLESPACE EncryptedTBs

DATAFILE ' /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf ' SIZE 100M

ENCRYPTION  <as USING clause is not mentioned, by default AES128 encryption is implemented>

DEFAULT STORAGE(ENCRYPT);

Temporary Tablespaces

-          - TEMP TBs are used to the sorting result set,  Temporary Tables/Indexes created etc.,

-          - Default TEMP TBs is shared by multiple users logged into the database. Default TEMP TBs currently in use can be queries from DATABASE_PROPERTIES table

-          - While creating the TEMP TBs, we should mention the TEMPFILE(not DATAFILE clause which we mention for permanent TBs creation) – in TEMPFILE created oracle just writes to the header & last block of the file, that’s why they are very quick to get created.

-          - For details about TEMPFILE use - V$TEMPFILE, V$TEMP_SPACE_HEADER and DBA_TEMP_FILES

-          - Monitor temporary segments using - V$SORT_SEGMENT, V$SORT_USAGE