How to Create TableSpaces - Oracle DBA

  • (4.0)
  • | 1624 Ratings |
  • Last Updated April 08, 2017

Overview of tablespaces

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. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A table space, in turn, consists of at least one data file which, in turn, are physically located in the file system of the server. A datafile belongs to exactly one tablespace.  Types of tablespaces in oracle.

  • Permanent tablespaces
  • Undo tablespaces
  • Temporary tablespaces

Using multiple tablespaces:

Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:

  • Separate user data from data dictionary data to reduce i/o contention.
  • Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
  • Store different the datafiles of different tablespaces on different disk drives to reduce i/o contention.
  • Take individual tablespaces offline while others remain online, providing better overall availability.
  • Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
  • Back up individual tablespaces.

Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

Creating tablespaces

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the system tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The system tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the system tablespace or take it offline.

The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles.

Checkout Oracle DBA Interview Questions

To create a new tablespace, use the sql statement create tablespace or create temporary tablespace. You must have the create tablespace system privilege to create a tablespace. Later, you can use the alter tablespace or alter database statements to alter the tablespace. You must have the alter tablespace or alter database system privilege, correspondingly.

You can also use the create undo tablespace statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a rollback statement.

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!


Related Articles:

Altering Tablespaces Availability - Oracle DBA

Managing The Undo Tablespace - Oracle DBA

Temporary Tablespaces - Oracle DBA

Subscribe For Free Demo

Free Demo for Corporate & Online Trainings. Protection Status