Tablespaces

In an Oracle database, a tablespace is a logical storage unit that groups related logical structures like tables and indexes together. It is a crucial part of the database’s architecture, allowing for organized and efficient data management.

Key Points about Tablespaces:

1. Logical Structure:
   • Tablespaces are used to logically group database objects, which can help in organizing and managing storage.
2. Physical Storage:
   • Each tablespace consists of one or more physical data files on the disk. The data files store the actual data.
3. Types of Tablespaces:
   • System Tablespace: Contains the data dictionary, which includes information about the database’s structure and objects.
   • User Tablespaces: Used to store user data. These can be further divided into default tablespaces for users and additional tablespaces for specific applications or users.
   • Temporary Tablespace: Used for temporary data like sorting operations.
   • Undo Tablespace: Stores undo information, which is necessary for transaction rollback and read consistency.
4. Space Management:
   • Tablespaces can be managed manually or automatically. Oracle supports two types of space management within tablespaces:
     • Dictionary-Managed: Space is managed using data dictionary tables.
     • Locally Managed: Space is managed using bitmaps, which can improve performance and reduce contention.
5. Tablespace Operations:
   • Creating a Tablespace: You can create a new tablespace using SQL commands, specifying its name, associated data files, size, and other properties.
   • Altering a Tablespace: You can add or remove data files, change the size of data files, and set other properties.
   • Dropping a Tablespace: You can drop a tablespace when it is no longer needed, which will also remove its associated data files.

Example SQL Commands:

• List all tablespaces with DBA privileges:

  SELECT tablespace_name 
  FROM dba_tablespaces;
  

• List all tablespaces with user-level privileges:

  SELECT tablespace_name 
  FROM user_tablespaces;
  

• Create a Tablespace:

  CREATE TABLESPACE mytablespace
  DATAFILE '/path/to/datafile.dbf' SIZE 100M;
  

• Add a Data File to a Tablespace:

  ALTER TABLESPACE mytablespace
  ADD DATAFILE '/path/to/another_datafile.dbf' SIZE 50M;
  

• Drop a Tablespace:

  DROP TABLESPACE mytablespace INCLUDING CONTENTS AND DATAFILES;
  

• Set a default tablespace for users:

  ALTER USER username DEFAULT TABLESPACE tablespace_name;
  

• Assign quotas to allow users to create objects:

  ALTER USER username QUOTA unlimited ON tablespace_name;
  

• Verify the tablespace is online and read-write:

  SELECT status, contents FROM dba_tablespaces 
  WHERE tablespace_name = 'your_tablespace';
  

• For specific objects, you can explicitly specify the tablespace:

  CREATE TABLE table_name (
    column1 datatype
  ) TABLESPACE tablespace_name;
  

Summary

Tablespaces are essential for logical storage management in Oracle databases. They help organize data, improve performance, and simplify database administration. By using tablespaces effectively, DBAs can ensure efficient storage allocation and maintenance.