Oracle - SPLessons

Chapter 8

Oracle Tablespace

Oracle Tablespace

A logical stockpiling container for segments is called Oracle Tablespace. The database objects are segments, like index and tables,and the storage space that it consumes. At the physical stage, information in one or more data files will be stored in the table space.

Permanent table space

A persistent schema objects are permanent table space groups. The Oracle Tablespace containing segments for objects are stored  in  the information files.

A default permanent Oracle Tablespace is assigned to each client in the database. The default SYSTEM and SYSAUX table spaces are used only by small databases. Hence, at least one table space for application data and to store user will be created by oracle. By using the  table spaces will accomplish the succeeding goals.

  • Allots an amount to a database clients.
  • Control disk space designation for database information.
  • Make a transportable table space that you can duplicate or move from one database to another, even across the stages.
  • Perform reinforcement and recuperation of single tables paces.
  • Take individual table spaces online or offline without affecting the availability of the whole database.
  • Import or fare application information by utilizing the Oracle Data Pump utility.

System table space

For administrative table space,System table space is necessary. When it is created it get included in the database. To manage this oracle database use system database.

The following information will be included in the SYSTEM table space, holds by the SYS client:

  • Incorporated stored objects like procedures, triggers and packages.
  • Tables and views that contain administrative data about the database.
  • The information word reference.

Any other table space will be managed by System table space, yet requires a more elevated amount of benefits and is limited in a few ways. For instance, the SYSTEM table space can’t be drop or rename. All newly created clients table spaces will be locally managed by oracle database. In a database with a locally managed SYSTEM table space, you cannot create dictionary-managed table spaces. Hence, execute the create database statement manually and accept the dictionary managed space in the SYSTEM table and can move the current word reference SYSTEM table space to a privately group space.

SYSAUX table space

The auxiliary table space to the SYSTEM table space is SYSAUX table space.Centralized location for database metadata will be provided by SYSAUX table space, the SYSTEM table space doesn’t reside at it. It table spaces created by the database will be reduced by default, both in the user-defined databases and in seed database. A few database segments,including Oracle Streams and Oracle Enterprise Manager utilize the table space SYSAUX as default stockpiling area. In the manner, the SYSAUX table space is made naturally amid upgrade or creation in the database.

Create view

Once a table is created from base table, all DML operations can be performed on that view which effects its base table.

Create any number of columns in a table. The system privileges are required to create own schema, and can execute any object privilege on object type.

create view <table_name>as select <column_name> FROM <table_name> where <condition>;

table_name => Any accurate table.

column_name => The columns that inserted in the table.

Condition => The condition to get the specific result set . 

By viewing the below example, the concepts of table spaces can be understood easily.

sql&gt; select * from cars;
| car_id | car_name | cost   |
|      1 | audi     |  52642 |
|      2 | skoda    | 526400 |
|      3 | volva    |  52640 |
|      4 | volva    |  52000 |
|      5 | hummer   |  41400 |
5 rows in set (0.00 sec)

sql&gt; create view cheapcars as select name from cars where cost&amp;lt;52640; 
ERROR 1054 (42S22): Unknown column 'name' in 'field list' 
sql&gt; CREATE VIEW Cheapcars AS SELECT car_name from cars where cost&amp;lt;52640; 
Query OK, 0 rows affected (0.13 sec) 
sql&gt; select * from cheapcars;
| car_name |
| volva    |
| hummer   |
2 rows in set (0.16 sec)

In the above example, a simple table cars has been created and by applying view operation to that table, like inserting some condition on column name.(i.e.,cost of car<52640 is the condition, and it show the cost of cars less than that value).  


In Oracle Logical Storage Structures, the logical unit of database storage space allocation made up of contiguous data blocks are called as extents. Extent containing the data blocks are legitimately coterminous however it can be expand out on disc as a result file system implementations and raid striping.

Assigning the underlying extent for a information fragment while the segment is made in the database. It constantly contained in one information document. Instead information is not combined to the segment, the information blocks in the underlying degree of extent are allotted for this segment solely. The primary data block contains the segment for each directory belongs to extents in the segment.

If primary extent turn out to be full,and required more space, it allocates automatically an incremental extent for this segment by the database. An incremental extent consequent made to this portion. The designation calculation relies on upon whether the table space is privately overseen or word reference oversees. In the privately oversaw case, the database looks the bitmap of an information document for nearby free pieces. In the event that the information document has lacking space, then the database looks in another information record. Degrees for a fragment are dependably in the same table space yet might be in various information records.

The database will assign extents for a segment for any information record in the table space. For instance, the portion can apportion the underlying degree in user1.dbf, dispense the principal incremental degree in users2.dbf, and spread the accompanying degree in user1.dbf.

Creating Tables Spaces

The CREATE TABLESPACE statement is utilized to create a tablespace, which is an assignment of space in the database that can contain schema objects and table space contain the different types

Permanent table space

The persistent schema objects will be contained in permanent tablespace. In data files, objects in permanent table space will be stored.

Temporary table space

The schema objects will be contained in temporary table space and stays only for a particular session duration. In temp files, objects in temporary tables pace will be stored.

Undo table space

A permanent table space is another type of undo table space, which is utilized by Oracle Database to oversee fix information in the event that you are running your database in programmed fix administration mode. Oracle empathetically prescribes that to utilize programmed fix administration mode opposed utilizing rollback segments for undo.

While creating a table space, it is generally used to read/compose table space. Subsequently perform the ALTER TABLESPACE statement to take the table space disconnected from the net or on the web, including information documents or temp records to it, or make it a read-only table space.

And simultaneously drop a table space from the database with the DROP TABLE SPACE statement.

More Table space Fun Facts

More table space fun facts consists of both permanent and temporary table space.

Table space can contain two types of data files, such as small files and big files.

ASSM – ASSM stands for Automatic segment space management, is a database default setting in which oracle control space allocation for blocks, extents and segments.

Locally managed table spaces are pretty much taken for granted nowadays_no need for dictionary managed table spaces unless you need backward compatibility.


  • Oracle Tablespace -Is logical stored container for segments.
  • Persistent table space – Are the persistent schema objects inside the table.
  • Creating table space – Is used to create a table space.