PL/SQL Tablespaces – Oracle PL/SQL Tutorial

In Oracle Database, a tablespace is a logical storage unit that helps manage database storage efficiently. It acts as an abstraction layer between physical storage (data files) and logical database structures like tables and indexes. Understanding tablespaces is essential for database administrators (DBAs) and developers working with PL/SQL.

What is a Tablespace?

A tablespace consists of one or more data files that store database objects such as tables, indexes, and partitions. Each tablespace belongs to a single database and helps in organizing data, optimizing performance, and managing storage allocation.

Types of Tablespaces in Oracle

Oracle classifies tablespaces into several categories based on their use:

System Tablespace: Contains core database metadata and is mandatory for database operation.

User Tablespaces: Stores user-created objects like tables, views, and indexes.

Temporary Tablespace: Holds temporary data for operations like sorting and joins.

Undo Tablespace: Manages undo records for transaction rollback and consistency.

SYSAUX Tablespace: Supports auxiliary database metadata, reducing the load on the SYSTEM tablespace.

Creating and Managing Tablespaces

DBAs can create and manage tablespaces using SQL and PL/SQL commands. Here’s how:

Creating a Tablespace

To create a tablespace, use the CREATE TABLESPACE statement:

CREATE TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/users01.dbf'
SIZE 100M
AUTOEXTEND ON
MAXSIZE 1G;

This command creates a tablespace named users with an initial size of 100MB, set to auto-extend up to 1GB.

Altering a Tablespace

To modify a tablespace, such as adding a data file, use:

ALTER DATABASE 
ADD DATAFILE '/u01/app/oracle/oradata/users02.dbf' SIZE 50M;

This increases the storage capacity of the users tablespace.

Dropping a Tablespace

To remove a tablespace and its associated data files:

DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

This permanently deletes the tablespace and its stored data.

Assigning Tablespaces to Users

In Oracle, each user must be assigned a default tablespace where their objects will be stored. Assign a tablespace using:

CREATE USER john IDENTIFIED BY password
DEFAULT TABLESPACE users;

This ensures the user’s objects are stored in the users tablespace.

Best Practices for Tablespace Management

Use Separate Tablespaces for Different Data Types: Store indexes and tables in separate tablespaces for performance optimization.

Enable Autoextend: Prevent storage errors by allowing data files to grow automatically.

Monitor Tablespace Usage: Regularly check tablespace usage with:

SELECT 
tablespace_name, file_name, bytes/1024/1024 AS size_MB 
FROM dba_data_files;

Use Locally Managed Tablespaces: They provide better space management and performance compared to dictionary-managed tablespaces.

Conclusion