Brief Introduction of Tablespace in Oracle 11g

Before creating a tablespace, you should create a database to contain it. The primary tablespace in any database is SYSTEM tablespace. It contains basic information to the functioning of the database server, like data dictionary and system rollback segment. The SYSTEM tablespace is the first tablespace formed at database creation, which is managed as any other tablespace, but it requires a superior level of privilege and it is restricted in several ways. You cannot drop or rename the SYSTEM tablespace or take this offline.

The SYSAUX tablespace acts as an auxiliary tablespace to SYSTEM tablespace. It is containing information about the schemas used by several Oracle products, such that those products do not need their own tablespaces. For the SYSTEM tablespace, management of the SYSAUX tablespace needs a higher security level and one cannot rename or drop this.

The procedures for creating tablespaces vary by different operating system, but the first step is always to use one operating system to make a directory structure in which user’s data files will be allocated. On most OSs users specify the size and fully mentioned filenames of data files when users create a new tablespace or alter an already existing tablespace by adding the data files. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the data files as specified.

To create a new tablespace, you need to use the SQL statement – ‘CREATE TABLESPACE’ or ‘CREATE TEMPORARY TABLESPACE’. One must have the ‘CREATE TABLESPACE’ system privilege to create tablespace. After that, you can ‘ALTER TABLESPACE’ or ‘ALTER DATABASE’ statement to alter tablespace. User must have the ‘ALTER TABLESPACE’ or ‘ALTER DATABASE’ privilege correspondingly.

There are several ways to maintain tablespaces –

  1. Locally Managed Tablespaces
  2. Bigfile Tablespaces
  3. Encrypted Tablespaces
  4. Temporary Tablespaces
  5. Multiple Temporary Tablespaces

BigClasses’s Oracle DBA online training comprises the important concepts Tablespaces in 11g includes all the concepts in a specific way to every individual learner. Our Oracle DBA online training by our expert faculties, high quality study materials and appropriate guidance. For more queries about our Oracle DBA training online contact us at


Share on FacebookShare on LinkedInTweet about this on TwitterGoogle+