Backend interview prep

Start

Oracle Tablespaces Interview Questions

Master Oracle Tablespaces with interview-focused questions covering Tablespaces, Data Files, Segments, Extents, Blocks, SYSTEM, SYSAUX, UNDO, TEMP, USERS, Bigfile Tablespaces, Autoextend, Storage Management, and production best practices.

Introduction

Oracle stores data in a well-organized storage hierarchy.

Unlike many databases, Oracle separates logical storage from physical storage.

Understanding Oracle storage architecture is critical for

  • Oracle DBAs
  • Database Engineers
  • Java Developers
  • Solution Architects

Oracle Storage Hierarchy

Database

↓

Tablespace

↓

Data File

↓

Segment

↓

Extent

↓

Block

Tablespaces provide

  • Logical Organization
  • Storage Isolation
  • Performance Management
  • Backup Flexibility
  • Security

Oracle Storage Architecture

flowchart TD

Database --> Tablespace

Tablespace --> DataFile

DataFile --> Segment

Segment --> Extent

Extent --> OracleBlock

1. What is a Tablespace?

Answer

A Tablespace is a logical storage container inside an Oracle database.

It contains one or more data files.

Applications never access data files directly.

Instead

Application

↓

Tablespace

↓

Data File

2. Why are Tablespaces required?

Tablespaces provide

  • Logical organization
  • Easier administration
  • Storage isolation
  • Better backup strategy
  • Better performance

3. What is a Data File?

A Data File is a physical file stored on disk.

It belongs to exactly one tablespace.

Example

users01.dbf

system01.dbf

Tablespace Architecture

flowchart LR

Tablespace --> DataFile1

Tablespace --> DataFile2

Tablespace --> DataFile3

4. Can one tablespace contain multiple data files?

Yes.

A tablespace can contain

multiple data files.

Example

USERS

↓

users01.dbf

↓

users02.dbf

↓

users03.dbf

5. Can one data file belong to multiple tablespaces?

No.

One data file belongs to

only one tablespace.

6. What is a Segment?

A Segment stores

database objects such as

  • Tables
  • Indexes
  • Undo
  • Temporary Data

Each object owns one segment.

Storage Hierarchy

Tablespace

↓

Segment

↓

Extent

↓

Block

7. What is an Extent?

An Extent is a collection of contiguous Oracle blocks.

Oracle allocates storage

extent by extent.

8. What is an Oracle Block?

Oracle Block is the smallest storage unit.

Typical sizes

  • 2 KB
  • 4 KB
  • 8 KB
  • 16 KB
  • 32 KB

8 KB is common.

Storage Hierarchy Diagram

flowchart TD

Tablespace --> Segment --> Extent --> Block

9. What is the SYSTEM Tablespace?

SYSTEM Tablespace stores

  • Data Dictionary
  • Internal Metadata
  • Core Database Objects

Created automatically during database creation.

Cannot be dropped.

10. What is the SYSAUX Tablespace?

SYSAUX is an auxiliary tablespace.

Stores

  • AWR
  • OEM Repository
  • Statistics
  • Performance Data

Reduces load on SYSTEM tablespace.

SYSTEM vs SYSAUX

SYSTEM SYSAUX
Data Dictionary Auxiliary Metadata
Mandatory Mandatory
Core Objects Performance Objects

11. What is the UNDO Tablespace?

UNDO stores

before-images

of modified rows.

Used for

  • Rollback
  • Read Consistency
  • Flashback Queries
  • Recovery

UNDO Workflow

flowchart LR

Transaction --> UNDO

UNDO --> Rollback

UNDO --> Flashback

12. Why is UNDO important?

UNDO enables

  • Transaction Rollback
  • Consistent Reads
  • Flashback Operations

Without sufficient UNDO

queries may fail.

13. What is the TEMP Tablespace?

TEMP stores temporary data for

  • Sorting
  • GROUP BY
  • ORDER BY
  • Hash Join
  • Temporary Objects

TEMP Usage

Large Sort

↓

TEMP Tablespace

↓

Result

14. What happens if TEMP is full?

Operations requiring temporary storage may fail.

Typical error

ORA-01652

Unable to extend temp segment

15. What is the USERS Tablespace?

Default tablespace

for application users.

Stores

  • Tables
  • Indexes
  • Objects

Created during installation.

Common Oracle Tablespaces

Tablespace Purpose
SYSTEM Data Dictionary
SYSAUX Auxiliary Metadata
UNDO Rollback Data
TEMP Temporary Data
USERS User Objects

16. What is a Bigfile Tablespace?

Contains

one very large data file.

Supports

terabytes of storage.

Useful for

large databases.

17. What is a Smallfile Tablespace?

Contains

multiple smaller data files.

Default option.

Most databases use Smallfile tablespaces.

Bigfile vs Smallfile

Bigfile Smallfile
One Large File Multiple Files
Easier Management More Flexible
Very Large Databases Default Choice

18. What is Autoextend?

Automatically increases

data file size

when space runs out.

Example

AUTOEXTEND ON

Autoextend

flowchart LR

DataFile --> SpaceFull --> AutoExtend --> Continue

19. Should Autoextend always be enabled?

Usually yes

but

always configure

MAXSIZE

to avoid consuming all disk space.

20. How do you create a Tablespace?

Example

CREATE TABLESPACE app_data

DATAFILE 'app_data01.dbf'

SIZE 500M

AUTOEXTEND ON

NEXT 100M

MAXSIZE 5G;

21. How do you add a Data File?

ALTER TABLESPACE app_data

ADD DATAFILE

'app_data02.dbf'

SIZE 1G;

22. How do you resize a Data File?

ALTER DATABASE

DATAFILE

'app_data01.dbf'

RESIZE 2G;

23. How do you check Tablespace usage?

Example

SELECT

tablespace_name,

status

FROM dba_tablespaces;

24. Important Data Dictionary Views

Useful views

  • DBA_TABLESPACES
  • DBA_DATA_FILES
  • DBA_FREE_SPACE
  • DBA_SEGMENTS
  • DBA_EXTENTS
  • DBA_TEMP_FILES

25. What is Tablespace Monitoring?

Monitor

  • Used Space
  • Free Space
  • Autoextend Status
  • Growth Rate
  • Temporary Usage

Tablespace Monitoring

flowchart LR

Tablespace --> Usage

Usage --> Alerts

Alerts --> DBA

26. Banking Example

Separate tablespaces

Customer

Transactions

Audit

Indexes

Improves administration and backup.

27. E-Commerce Example

Store

Orders

Products

Customers

Logs

in different tablespaces.

28. Production Example

Problem

Tablespace Full

Solution

  • Add Data File
  • Resize Existing File
  • Enable Autoextend
  • Archive Old Data

29. Common Tablespace Problems

  • Tablespace Full
  • TEMP Exhaustion
  • UNDO Too Small
  • Autoextend Disabled
  • Storage Fragmentation
  • Large Segments

30. Enterprise Storage Design

Typical layout

SYSTEM

SYSAUX

UNDO

TEMP

APP_DATA

APP_INDEX

APP_AUDIT

APP_LOG

Separate application data and indexes into different tablespaces for better administration.

Enterprise Best Practices

  • Keep SYSTEM tablespace for Oracle objects only.
  • Never store application tables in SYSTEM.
  • Separate data and indexes into different tablespaces.
  • Monitor free space continuously.
  • Enable Autoextend with MAXSIZE.
  • Size TEMP tablespace based on workload.
  • Allocate sufficient UNDO for long-running transactions.
  • Monitor tablespace growth proactively.
  • Archive historical data regularly.
  • Use Bigfile tablespaces only when appropriate.

Quick Revision

Topic Key Point
Tablespace Logical Storage Container
Data File Physical Storage File
Segment Stores Database Objects
Extent Collection of Blocks
Block Smallest Storage Unit
SYSTEM Data Dictionary
SYSAUX Performance Metadata
UNDO Rollback Data
TEMP Sorting & Temporary Work
USERS User Objects
Bigfile Single Large Data File
Autoextend Automatic Growth

Interview Tips

Interviewers frequently ask

  • What is a Tablespace?
  • Tablespace vs Data File.
  • Explain Oracle Storage Hierarchy.
  • SYSTEM vs SYSAUX.
  • What is UNDO?
  • What is TEMP?
  • Bigfile vs Smallfile Tablespaces.
  • What is Autoextend?
  • How do you monitor tablespaces?
  • Explain Oracle storage architecture.

A strong interview explanation is:

"Oracle organizes storage using a hierarchy of Tablespaces, Data Files, Segments, Extents, and Blocks. Tablespaces provide logical storage management, while Data Files provide physical storage. SYSTEM stores Oracle metadata, SYSAUX stores auxiliary components, UNDO supports transaction rollback and read consistency, TEMP is used for sorting operations, and USERS stores application objects."

Summary

Oracle Tablespaces provide a logical layer for organizing and managing physical database storage. They simplify administration, improve backup strategies, enhance performance, and isolate different types of data. Understanding the Oracle storage hierarchy—including Tablespaces, Data Files, Segments, Extents, and Blocks—along with specialized tablespaces such as SYSTEM, SYSAUX, UNDO, and TEMP, is essential for Oracle Developers, Database Engineers, DBAs, and Solution Architects working in enterprise Oracle environments.