ORACLE TABLESPACE

ORACLE: TABLESPACES with examples

Tablespaces are used to store database objects that take up space on disks. These objects are called segments. Examples of segments include tables, indexes, clusters, undo segments, and materialized views. Database objects that do not take up space on disks are stored in the data dictionary. Examples of these objects include triggers, functions, stored procedures, and database links.

Tablespaces are also used to store segments in a logical manner. You could store every database segment in one tablespace. This would be similar to placing all of your workstation’s files in one giant folder. Tablespaces let you better manage your database segments. When segments are in multiple tablespaces, you have more flexibility. For instance, you can:

• Separate a schema’s segments from other schema segments.
• Balance I/O load among different physical disk devices.
• Separate the data dictionary, undo segments, temporary segments, and application segments from interfering with each other.
• Make certain segments READ ONLY by placing them in a READ ONLY tablespace. This protects from unwanted changes to data.
• Backup certain segments by backing up just the tablespace that contains those segments.
• Limit the amount of data a schema can store with tablespace quotas.

Space Allocation Recommendations

There are two ways that you can manage space allocation of segments stored in a tablespace. The old way was to track the allocated space and the free space in a tablespace with the data dictionary. This method is called Dictionary Managed Tablespace, or DMT. Using DMTs required space allocation operations to use two data dictionary tables, SYS.UET$ for tracking used extents, and SYS.FET$ for tracking free extents. These two tables could become a bottleneck for frequent space allocation and deallocation requests.

Starting with the Oracle 8i release, a new space allocation method was created. This method is called Locally Managed Tablespace, or LMT. LMTs track space allocation using bitmaps in the datafile’s header, or locally in the tablespace. The data dictionary tables are no longer bottlenecks for space allocation and deallocation requests.

When creating an LMT, you have two options to define how Oracle creates extents in the tablespace. The first option is to force each extent in the tablespace to be the same exact size. This is UNIFORM space allocation. The other option is to let the database determine the best size for your extent automatically with the AUTOALLOCATE option. With AUTOALLOCATE, the database first starts allocating smaller extents. After you’ve requested a number of small extents, the database starts allocating larger extents. Early in LMTs history, many Oracle experts would only use UNIFORM space allocation. But more and more, these experts are now switching to using AUTOALLOCATE exclusively.

Oracle is recommending that all tablespaces be created as LMTs. Future versions of Oracle will require doing away with DMTs, leaving LMTs as the only space allocation method for tablespaces. The Oracle 9.2.0 release was the first one that let the SYSTEM tablespace become an LMT. If you choose to create the SYSTEM tablespace as an LMT, then you cannot have any DMTs in your database. If you want DMTs in your database, then your SYSTEM tablespace must be a DMT.

Creation Recommendations

• As stated above, it is recommended that a Locally Managed Tablespace be created. You can use either allocation method. With UNIFORM extent sizes, each extent in the tablespace will be exactly the same size. With AUTOALLOCATE, the database decides the best size for the extent. The AUTOALLOCATE relieves the DBA from the task of determining the best size for the extents.

• If COMPATIBLE is set to 9.0.0 or higher, the default space management is Locally Managed. If COMPATIBLE is set less than 9.0.0, the default space management is set to Dictionary Managed. The defaults can be overridden by the EXTENT MANAGEMENT clause.

• The UNDO option was introduced in Oracle 9i to create a tablespace to hold system-managed undo segments. This relieves the DBA from the task of determining the optimal settings and the number of rollback segments. In order to use UNDO tablespaces, the UNDO_MANAGEMENT parameter should be set to AUTO. The UNDO_TABLESPACE needs to be set to a tablespace name that we specifically set for undo segments. You will not be able to create objects in this tablespace. The database will automatically create undo segments for you.

• Beginning with the Oracle 9i version, the BLOCKSIZE parameter lets you create tablespaces with block sizes that differ from your database’s default block, specified by the DB_BLOCK_SIZE parameter. In order to use this option, you need to set DB_CACHE_SIZE instead of the older DB_BLOCK_BUFFERS and you need to have DB_nK_CACHE_SIZE set, where ‘n’ is the block size you specify above.

• The NOLOGGING clause is often misunderstood. Many people take this to mean that any changes to the data in segments stored in this tablespace will not be logged to the database’s online redo logs. When the tablespace is set to NOLOGGING, only certain operations (creation of objects, direct path inserts, and direct loads of SQL*Loader) are not logged. Regular DML statements (INSERT, UPDATE, DELETE) are always logged.

Sizing Insights

• Tablespaces should be sized to contain all of the objects they are intended to hold. This means that in order to size a tablespace properly you will need to size all of the objects that will be placed in the tablespace first. If the tablespace will contain large objects (LOBs) it may be difficult to get an accurate size estimate.

• The AUTOEXTEND capability can make some tablespace management easier but it is no replacement for properly sizing tablespaces. One problem with the AUTOEXTEND is that you have no way of knowing when you will run out of space due to a runaway process (for example a cartesion product of two million row tables filling the temporary tablespace ). 

• Once you have the sizes of all the objects that will reside in a tablespace, add these size figures together and add 25-50% additional room to allow for growth. We recommend that the default storage parameters for a tablespace not be used except in the case where the tablespace purpose is to hold rollback segments.

Datafile Insights

• Tablespaces provide storage for all database objects such as tables, indexes and clusters. To create a tablespace contiguous space must be available in order to create a tablespace datafile.

• The maximum number of tablespaces for a database is controlled by the maximum number of datafiles. The maximum number of datafiles is controlled via the MAXDATAFILES parameter in the CREATE DATABASE command. Ensure it is set properly or you may have to re-create the database or the control files.

• If you add new data files to a tablespace and do not fully specify the filenames, ORACLE creates the data files in the default directory of the database server. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files; the old files will be overwritten and all of the data lost.

Process Insight

• The first tablespace, SYSTEM, is automatically created when the database instance is created. Tablespaces subsequent to SYSTEM must be created manually (unless your database has been provided by a third party vendor, in that case, the database instance tablespaces may already be created with SYSTEM).

• You should never leave SYSTEM as the only tablespace as this can lead to data corruption and system failure.

CREATE TABLESPACE syntax

This topic contains the following versions of the CREATE TABLESPACE syntax:

• V10.1-10.2
• V8.1-9.2
• V7.3-8.0

CREATE TABLESPACE Syntax V10.1-10.2

Description     Example

ORACLE TABLESPACE

CREATE TABLESPACE Syntax V8.1-9.2

Description    Example

ORACLE TABLESPACE

Notes: filespec was changed to datafile_temfile_spec with Oracle9.2. Also with Oracle9.2, the FORCE LOGGING and COMPRESS | NOCOMPRESS clauses were added.

CREATE TABLESPACE Syntax V7.3-8.0

Description

ORACLE TABLESPACE

Syntax diagrams and parameter descriptions adapted from Oracle, Inc. documentation.

CREATE TABLE SPACE EXAMPLES

Dictionary Managed Example:

In this example we'll create a data tablespace for the accounts receivable (AR) application in our accounting package. The database for our AR application is called 'ORACTP' short for Oracle Accounting Production database. Let's look at the actual command to create a tablespace with an initial 500 megabyte datafile with autoextention to a maximum size of 1 gigabyte (1024 megabytes). The tables in our AR application will hold a fairly large amount of data so just in case someone forgets to size their tables let's size the default storage to INITIAL 10M NEXT 1M PCTINCREASE 10.

CREATE TABLESPACE ar DATAFILE '\ORACLE1\ORACTP\data\oractp_ar01.dbf' SIZE 500M

AUTOEXTEND ON NEXT 200M MAXSIZE 1024M

DEFAULT STORAGE (INITIAL 10M NEXT 1M PCTINCREASE 10)

PERMANENT

ONLINE

LOGGING;

We've included the PERMANENT, ONLINE and LOGGING clauses for illustration only. They are the default if nothing is specified. We specified a PCTINCREASE value because if it is set to zero the SMON process will not coalesce free space. Also, if PCTINCREASE is not specified, it will default to 50%, therefore specifying it at a low value is suggested.

Locally Managed Example:

If we wanted the extents to be locally managed due to the level of dynamic allocation, the CREATE TABLESPACE clause will change in that we no longer specify the DEFAULT STORAGE clause and instead we use the EXTENT MANAGEMENT clause with the LOCAL clause. If we want to ensure that uniform extents are generated then we can specify the UNIFORM clause as well. Let's aim for 2 megabyte LOCAL UNIFORM extent management.

CREATE TABLESPACE ar

   DATAFILE '\ORACLE1\ORACTP\data\oractp_ar01.dbf'

   SIZE 500M

   AUTOEXTEND ON NEXT 200M MAXSIZE 1024M

   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M

   PERMANENT

   ONLINE

   LOGGING;

Other Examples:

A full CREATE TABLESPACE command for Unix might look like:

CREATE TABLESPACE USER

   DATAFILE 'm_disk1/m_oracle/databases/db_test/ora_user_1.dbf'

   SIZE 10M REUSE

   DEFAULT STORAGE (MINEXTENTS 1

                    MAXEXTENTS 60

                    INITIAL 50K

                    NEXT 50K

                    PCTINCREASE 10)

   ONLINE;

While one for VMS would differ only in the datafile format:

CREATE TABLESPACE USER

   DATAFILE 'M_DISK1:[M_ORACLE.DATABASES.DB_TEST]ORA_USERS_1.DBS'

   SIZE 10M REUSE

   DEFAULT STORAGE (MINEXTENTS 1

                    MAXEXTENTS 60

                    INITIAL 50K

                    NEXT 50K

                    PCTINCREASE 10)

   ONLINE;

For Unix, the file name is case sensitive, for VMS it is not.

Striped Tablespace Example:

For a striped datafile set, one that encompasses two or more disks or file systems, the same command is used, only a second line is added to the datafile command option:

CREATE TABLESPACE USER

   DATAFILE

      'M_DISK1:[M_ORACLE.DATABASES.DB_TEST]ORA_USERS_1.DBS' SIZE 10M REUSE,

      'M_DISK2:[M_ORACLE.DATABASES.DB_TEST]ORA_USERS_2.DBS' SIZE 10M REUSE

   DEFAULT STORAGE (MINEXTENTS 1

                    MAXEXTENTS 60

                    INITIAL 50K

                    NEXT 50K

                    PCTINCREASE 10)

   ONLINE;

Rollback Segment Tablespace Example:

If a tablespace is to hold rollback segments only, the default storage clause can be tailored so that the only part of the CREATE ROLLBACK SEGMENT command that has to be used is the name and tablespace specifications:

CREATE TABLESPACE ROLLBACKS

   DATAFILE 'M_DISK3:[M_ORACLE.DATABASES.DB_TEST]ORA_ROLLBACKS_1.DBS'

   SIZE 60M REUSE

   DEFAULT STORAGE (MINEXTENTS 2

                    MAXEXTENTS 20

                    INITIAL 500K

                    NEXT 500K

                    OPTIMAL 10)

   ONLINE;

Since the rollback storage parameters will now default to where we want them, the CREATE ROLLBACK SEGMENT command would simplify to:

CREATE ROLLBACK SEGMENT ROLLBACK1

TABLESPACE ROLLBACKS;

In this case, if a rollback segment extended beyond 10 extents, it would automatically shrink back to 10 when the process finished with it.

CREATE TEMPORARY TABLESPACE SYNTAX

This topic contains the following versions of the CREATE TEMPORAR"Y TABLESPACE syntax:

• V9.0-V9.2
• V8.1

CREATE TEMPORARY TABLESPACE V9.0-V9.2

Description

ORACLE TABLESPACE

CREATE TEMPORARY TABLESPACE V8.1

Description    Example

ORACLE TABLESPACE

Syntax diagrams and parameter descriptions adapted from Oracle, Inc. documentation.

CREATING AN UNDO TABLESPACE

Whenever a transaction is initiated, it can be rolled back to bring it back to its original state. This original state of data is stored in rollback (UNDO) segments which are created in a tablespace called UNDO or Rollback Tablespace. The data written in these segments are called Undo Records. These Undo records are very critical for the database to function properly and are used to:

1. Rollback the transaction to its original state whenever ROLLBACK is issued or the transaction fails abruptly due to loss of database connectivity or instance crash.

2. Recover the database.

3. Provide Read Consistent Image of the data, so that read do not block reads.

4. Recover an Object to an earlier point in time using Flashback Query.

To use the automated UNDO management features, you must first create an UNDO tablespace. The UNDO tablespace can be created by using the UNDO clause of the CREATE TABLESPACE command or by using the CREATE DATABASE command. The sections below will look at each of these methods in greater detail.

Creating an UNDO Tablespace Using the CREATE TABLESPACE Command

The example below illustrates how to use the UNDO clause of the CREATE TABLESPACE command to create an UNDO tablespace:

CREATE UNDO TABLESPACE undo_tbs

DATAFILE '/ora100/oracle/mydb/data/mydb_undo_tbs_01.dbf' SIZE 100m

AUTOEXTEND ON;

The UNDO tablespace created by this example is called UNDO_TBS. The CREATE UNDO TABLESPACE command syntax is very similar to the CREATE TABLESPACE command, including the datafile and size clauses. Only the datafile clause and a restricted form of the extent managment clause of the CREATE TABLESPACE command can be used when creating an UNDO tablespace. This means that no default storage characteristics can be defined. It is also worthwhile to note that Oracle creates the UNDO tablespace as a locally managed tablespace; there is no option to create it as a dictionary-managed tablespace.

After an UNDO tablespace has been created, it, along with the undo segments within it, will be brought on line each time the database is started. Messages that appear in the alert log each time the database is started will show this.

Note: Even if you create an UNDO tablespace, you will still need to keep the SYSTEM rollback segment.

Creating an UNDO Tablespace Using the CREATE DATABASE Command

The CREATE DATABASE command has been modified to support the definintion of UNDO tablespaces during the database creation process. This is done using the undo tablespace clause as shown in the example below:

CREATE DATABASE mydb

CONTROLFILE REUSE

LOGFILE GROUP 1 ('d:\oradata\mydb\redo\mydb_redo_01a.log',

                 ' e:\oradata\mydb\redo\mydb_redo_01b.log ')  SIZE 50K,

        GROUP 2 ('d:\oradata\mydb\redo\mydb_redo_02a.log',

                 ' e:\oradata\mydb\redo\mydb_redo_02b.log ')  SIZE 50K

MAXINSTANCES 1 MAXLOGFILES 5  MAXLOGHISTORY 100 MAXDATAFILES 100

ARCHIVELOG

DATAFILE  'e:\oradata\mydb\mydb_system_01.dbf'

SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED,

DEFAULT TEMPORARY TABLESPACE temp_ts

TEMPFILE  'e:\oradata\mydb\mydb_temp_ts_01.dbf' SIZE 20m

UNDO TABLESPACE undo_ts DATAFILE  'e:\oradata\mydb\mydb_undo_ts_01.dbf'

SIZE 50M AUTOEXTEND OFF;

The UNDO tablespace created in this example is called undo_ts. By using the DATAFILE clause, we were able to define the name and location of the datafile associated with this UNDO tablespace. Note that the SIZE and AUTOEXTEND clauses.have also been included.

When using the CREATE DATABASE command, there are a couple of rules to consider relating to UNDO tablespaces. These rules differ depending on how the database is configured.

1. If the database instance is not configured for automated UNDO management, and the UNDO TABLESPACE clause is ommitted, the CREATE DATABASE statement will work as it always has -- no UNDO tablespace will be created.

2. If the instance is configured for automated UNDO management, the default behavior of the CREATE DATABASE statement changes. If you do not include the undo tablespace clause, Oracle creates an UNDO tablespace by default. The tablespace will be called SYS_UNDOTBS. The tablespace will be created with a default size of 100M for the database datafile.

Dropping an UNDO Tablespace

You can use the DROP TABLESPACE command to drop an UNDO tablespace. If the UNDO tablespace happens to be the active UNDO tablespace, Oracle will generate an error at this command

ALTER TABLESPACE

Many times you will want to change the tablespace’s characteristics after the tablespace has been created. For example, you might wish to make a tablespace READ ONLY or READ WRITE. Or you may wish to make a change to the tablespace’s availability.

Alter Tablespace Recommendations

• Making changes to the tablespace is not a recoverable operation. It is always a good idea to backup the tablespace before and after the operation.

• The BEGIN BACKUP command lets you perform a hot backup of an active tablespace. When you are done backing up that tablespace, let the database know with the END BACKUP command. While the tablespace is in backup mode, any DML activity on that tablespace’s segments will cause an increase in the amount of redo generated. It is good practice to only have one tablespace in backup mode at any given time.

Characteristics

• The RENAME DATAFILE clause will let you rename a tablespace’s datafiles. The database must be OPEN and the tablespace must be OFFLINE before this operation can proceed. This clause does not change the file’s name on the operating system. You must use OS commands to rename the file.

• The DEFAULT storage clause is only used for Dictionary Managed Tablespaces.

• The COALESCE clause is used to prompt SMON to coalesce contiguous free extents into larger free extents. Locally managed tablespaces do not need coalescing of free extents.

Restrictions

• The ALTER TABLESPACE system privilege is required for all options of this command.

• The MANAGE TABLESPACE system privilege will only allow changing the tablespace’s availability (ONLINE, OFFLINE), to begin or end a tablespace backup, or to make the tablespace READ ONLY or READ WRITE.

• The ALTER TABLESPACE command cannot be used to change the space allocation management (DMT or LMT). For that, you need to use the DBMS_SPACE_ADMIN supplied package.

• Before you can make a tablespace READ ONLY, it must be online, not contain any undo segments, and not be in backup mode.

Renaming Tablespaces

Renaming tablespaces is the most wanted feature for years in Oracle Database. Oracle10g database allows renaming a tablespace with a simple ALTER TABLESPACE command. The following SQL command renames the USER tablespace to USERS tablespace. This feature requires the init.ora parameter COMPATIBLE set to 10.0 or higher.

ALTER TABLESPACE USER RENAME TO USERS;

The RENAME TABLESPACE command should not be used to rename the tablespaces if the Oracle Managed Files (OMF) feature is used.In Oracle Managed Files the datafiles contain the tablespace names in the datafiles. Renaming a tablespace just updates the definition in the data dictionary and does not propagate the changes to the datafiles.

ALTER TABLESPACE and Backups

• When doing an online or hot backup you should only take one tablespace into backup mode at a time to reduce the required redo log space and minimize problems if a crash should occur during the backup.

• If using ALTER to place a tablespace in BEGIN BACKUP, be sure that the backup procedure backs up all the redo and archive logs as well.

• Immediately after the backup concludes, bring the tablespace back to normal with the END BACKUP command, or the redo logs will get out of sync and the file will not be recoverable.

• If a tablespace is left in backup mode after an online or hot backup, that table will not be recoverable should a crash occur.

• Before a recovery, be sure that all tablespaces are altered online or they may not be recoverable.

ALTER TABLESPACE SYNTAX

This topic contains the following versions of the ALTER TABLESPACE syntax diagram:

·        V10.1-10.2

·        V9.2

·        V9.0

·        V8.1

·        V7.3-8.0

ALTER TABLESPACE Syntax V10.1-10.2

Description     Example

ORACLE TABLESPACE

ALTER TABLESPACE Syntax V9.2

Description    Example

ORACLE TABLESPACE

ALTER TABLESPACE Syntax V9.0

Description    Example

ORACLE TABLESPACE

ALTER TABLESPACE Syntax V8.1

Description    Example

ORACLE TABLESPACE

ALTER TABLESPACE Syntax V7.3-8.0

Description

ORACLE TABLESPACE

Syntax diagrams and parameter descriptions adapted from Oracle, Inc. documentation.

Example use of ALTER TABLESPACE to add Datafiles

If we wish to add a 10 megabyte file to a tablespace called USERS on a VMS platform, the command would be (assuming the disk is logically named M_DISK1 and an OFA compliant naming scheme has been used):

ALTER TABLESPACE USERS

ADD DATAFILE

'M_DISK1:[M_ORACLE.DATABASES.DB_TEST]ORA_USERS_1.DBS' SIZE 10M

REUSE;

For a Unix system, the only change would be to substitute the proper file path format for the VMS formatted path in the above command.

Using ALTER TABLESPACE to rename a Datafile

The RENAME option for the ALTER TABLESPACE command is used, as its name implies, to rename a datafile associated with a tablespace. This would be used when you may have made a mistake naming the file initially, or, more importantly, when you need to move a tablespace. A tablespace (other than SYSTEM) must be offline for you to be able to rename it. The general procedure for renaming a tablespace is:

1. 1Alter the tablespace to have a datafile renamed offline:

SQL> ALTER TABLESPACE TEST OFFLINE;

2. Use the ALTER TABLESPACE command to rename the problem file:

SQL> ALTER TABLESPACE TEST

1: RENAME

2: 'm_disk1/m_oracle/databases/db_test/ora_test_3.dbf' TO

3: 'm_disk1/m_oracle/databases/db_test/ora_test_2.dbf';

3. Use the ALTER TABLESPACE command to bring the tablespace back online:

SQL> ALTER TABLESPACE TEST ONLINE;

Using ALTER TABLESPACE to move a Datafile

Sometimes it may be required that a datafile be moved. In cases where a disk has errors or perhaps just become too full, or you need to re-organize your disks you may need to move datafiles. The general procedure for moving datafiles is:

1. Take the problem tablespace offline:

SQL> ALTER TABLESPACE TEST OFFLINE;

2. Use the operating system file copy command to move the datafile to its new location:

UNIX:

$cp m_disk1/m_oracle/databases/db_test/ora_test_1.dbf \

$ m_disk2/m_oracle/databases/db_test/ora_test_1.dbf

VMS:

$ COPY M_DISK1:[M_ORACLE.DATABASES.DB_TEST]ORA_TEST_1.DBS -

_$ M_DISK2:[M_ORACLE.DATABASES.DB_TEST]ORA_TEST_1.DBS

3. Use the alter tablespace command to rename the datafile:

SQL> ALTER TABLESPACE TEST

RENAME DATAFILE

'm_disk1/m_oracle/databases/db_test/ora_test_1.dbf' TO

'm_disk2/m_oracle/databases/db_test/ora_test_1.dbf';

4. For Unix, alter the mode of the old datafile to make it non-readable:

$ chmod a - r m_disk1/m_oracle/databases/db_test/ora_test_1.dbf

This is done so that you can test the move before deleting the Unix file. Under VMS this step isn't required since you can't delete an open file.

5. Alter the tablespace back online:

SQL> ALTER TABLESPACE TEST ONLINE;

If the rename was successful, there should be no errors, if it wasn't, the tablespace will not come online under Unix since you can't read the original file, under VMS it will open the old file if it was unsuccessful and you won't be able to tell until step 6 if you were successful.

6. Delete the old tablespace datafile:

Unix:

$ rm m_disk1/m_oracle/databases/db_test/ora_test_1.dbf

VMS:

$ DELETE M_DISK1:[M_ORACLE.DATABASES.DB_TEST]ORA_TEST_1.DBS

If the rename wasn't successful under VMS you won't be able to delete the old datafile since it will be open.

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions