Working on Oracle Tablespaces for Developers

A database, as defined by Oracle, is an organized collection of information typically stored electronically in a computer system. The explanation is simple, however, it’s not that simple to store the data.

The Database has a tablespace that stores all the data. Everything inside a database is stored in tablespace.

Tablespaces

What is a Tablespace?

Tablespace in Oracle Database are logical storage units to store all the data. Logical storage units help users locate specific data and help in the retrieval of data.

Types of Tablespaces

• SYSTEM Tablespace – Oracle Database contains SYSTEM tablespace which is automatically created when the database is created. The tablespace contains a data file that stores data dictionary tables for the entire DB.
• Undo Tablespaces – These tablespaces store the undo information and nothing else.

Table Space

Relationship Between PLSQL Developer and Oracle Tablespaces

The creation and extension of the tablespaces is part of DBA’s responsibilities but as an Oracle PL/SQL Developer, we might come across cases when the tablespace have issue and that’s where a simple knowledge about the tablespaces can help reduce the burden on the database.

Issues with Oracle Tablespaces

Tablespace comes with a standard size and can be used be fully over a period of time. So below are the issues with the Tablespaces when it is nearly full or completely full.

Below are the known issues:

• The developer will not be able to create any new table
• The developer will not be able to create any other Oracle object.
• You will not be able to add – Insert any data into table

DBA Part of Solution

Once the tablespace is full, DBA will extend it using the below statement.

ALTER TABLE Tablespace_Name ADD DATAFILE ‘Data_File_Location’ size 40M AUTOEXTEND ON;

However, there are chances that DBA creates a new tablespace and then asks developers to move their backups from the old tablespace to a new, create new backup into the new tablespace.

PLSQL Developer’s Part

Developers have an additional responsibility of maintaining data in tablespaces, so that performance remain efficient. Below are the actions that developers can take.

Move the already present table to a new tablespace.

ALTER TABLE <TABLE_NAME> MOVE TABLESPACE <NEW_TABLESPACE_NAME>

Since the above command will invalidate all the table’s indexes, make sure you run the below command as well.

ALTER INDEX <INDEX_NAME> REBUILD;

Create table backup into new tablespace.

CREATE TABLE <BACKUP_TABLE_NAME> 
TABLESPACE <NEW TABLESPACE_NAME> AS 
SELECT * FROM <TABLE FOR WHICH BACKUP IS REQUIRED>;

Create Indexes into new tablespace.

CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (COLUMNS …) TABLESPACE <NEW TABLESPACE_NAME>

I hope this will help the Oracle PL/SQL Developers.

Note: It’s always a good practice to create tables, indexes, packages, and all other Oracle objects by mentioning the tablespace. It has been seen by senior devs, that new developers often create temporary tables or normal tables without mentioning the tablespaces.

Frequently Asked Questions on Tablespaces – FAQs

Is it mandatory to create tables or index with the tablespace?

Whenever an object is created without mentioning the tablespace name, it gets stored in the default tablespace which causes it to get full early. Thus, it is good practice to add a tablespace name while creating an Oracle object.

Do we really need to care about Tablespace, can’t we add more storage when the primary one is full?

Definitely, DBAs can extend the datafiles or add another tablespace. However, storage comes with a price. More the storage, the higher the price.