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.
Using multiple tablespaces:
Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:
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.
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.
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.
Get Updates on Tech posts, Interview & Certification questions and training schedules