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.
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 a 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 –
- Locally Managed Tablespaces
- Bigfile Tablespaces
- Encrypted Tablespaces
- Temporary Tablespaces
- Multiple Temporary Tablespaces
About Oracle Online Training @ BigClasses
BigClasses is one of the best online training organizations offer Oracle training. We have qualified and experienced faculties who are responsible for taking the online sessions. We provide study materials and 24 hours support to our national and international learners as well. If you are interested in Oracle online training, contact us for the detailed course and the free demo classes.
India: +91 800 811 4040 USA: +1 757 905 2515