Oracle Tablespace & Storage Management

ADVERTISEMENT

Managing tablespaces and storage is key to keeping your Oracle database organized and efficient. This post covers important concepts like tablespaces, datafiles, segments, extents, and storage best practices every DBA should know.

📦 Tablespace Basics

1. What is a tablespace in Oracle?
A tablespace is a logical storage container that holds database objects like tables and indexes. It maps to one or more physical datafiles.

2. What are the different types of tablespaces in Oracle?

  • Permanent – For storing user data.
  • Temporary – For sorting and joins.
  • Undo – For transaction rollback.
  • System & SYSAUX – Core database metadata and tools.

3. What is the purpose of the SYSTEM and SYSAUX tablespaces?

  • SYSTEM stores critical data dictionary objects.
  • SYSAUX offloads non-critical data from SYSTEM (like AWR).

4. What is the difference between TEMP and UNDO tablespaces?

  • TEMP stores intermediate data (e.g., sorting).
  • UNDO holds data for rollback and read consistency.

5. What is the default tablespace for a user and how is it set?
It’s where user objects are stored by default.

CREATE USER test IDENTIFIED BY pwd DEFAULT TABLESPACE users;

📂 Datafiles and Tablespace Structure

6. What is a datafile and how is it related to a tablespace?
A datafile is a physical file on disk that stores tablespace data.

7. Can a tablespace have multiple datafiles?
Yes. Except for BIGFILE tablespaces, which support one large datafile.

8. How do you add a datafile to an existing tablespace?

ALTER TABLESPACE users ADD DATAFILE '/path/file02.dbf' SIZE 100M;

9. What happens if a datafile becomes full?
Inserts may fail unless auto-extend is enabled or the file is resized.

10. How do you resize a datafile or enable auto-extend?

ALTER DATABASE DATAFILE 'file.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

🧱 Logical Storage: Segments, Extents, Blocks

11. What is the difference between a block, extent, and segment?

  • Block – Smallest storage unit.
  • Extent – Group of blocks.
  • Segment – Full object (e.g., table), made of extents.

12. What types of segments exist in Oracle?
Table, index, rollback, temporary, and undo segments.

13. How is space allocated to segments in Oracle?
Automatically via extents, when more space is needed.

14. What is locally managed vs dictionary managed tablespace?

  • Locally managed – Tracks extents using bitmaps (recommended).
  • Dictionary managed – Uses data dictionary (deprecated).

🧮 Undo and Temporary Tablespaces

15. What is the role of an UNDO tablespace?
Stores undo data to roll back transactions and support read consistency.

16. How does Oracle manage undo segments?
Automatically allocates and reuses undo segments in AUTO mode.

17. What is a temporary tablespace and how is it used?
Used for sorting operations, temporary tables, and joins.

18. Can a user have multiple temporary tablespaces?
Yes, with a temporary tablespace group.

🧰 Tablespace Management and Monitoring

19. How do you check tablespace usage in Oracle?
Use views like DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_TABLESPACE_USAGE_METRICS.

20. How do you move or rename a tablespace?
Rename tablespace:

ALTER TABLESPACE oldname RENAME TO newname;

21. How do you drop or make a tablespace read-only?

  • Drop: DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
  • Read-only: ALTER TABLESPACE users READ ONLY;

22. What is the difference between BIGFILE and SMALLFILE tablespaces?

  • BIGFILE: One large datafile (up to TBs).
  • SMALLFILE: Multiple smaller datafiles.

23. How do you monitor fragmentation in tablespaces?
Use DBA_FREE_SPACE, DBMS_SPACE package, or AWR reports.

🔒 Security and Best Practices

24. Can tablespaces be encrypted? How?
Yes, using Transparent Data Encryption (TDE).

ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

25. What is tablespace point-in-time recovery (TSPITR)?
Allows recovery of a specific tablespace to a past point in time without affecting others.

26. What are some best practices in tablespace management?

  • Use locally managed tablespaces.
  • Separate data and index tablespaces.
  • Enable auto-extend with limits.
  • Monitor regularly.

🔧 Commands & Tools

27. What are common SQL commands for tablespace management?

  • CREATE TABLESPACE
  • ALTER TABLESPACE
  • DROP TABLESPACE
  • ALTER DATABASE DATAFILE

28. What views are used to monitor tablespaces and datafiles?

  • DBA_TABLESPACES
  • DBA_DATA_FILES
  • DBA_FREE_SPACE
  • V$TABLESPACE

29. How do you move a table from one tablespace to another?

ALTER TABLE tablename MOVE TABLESPACE new_ts;

30. What tools does Oracle provide for managing tablespaces?

  • SQL*Plus
  • Oracle Enterprise Manager (OEM)
  • Data Dictionary Views
  • RMAN (for recovery)

ADVERTISEMENT