Database Storage Structures

🧠 Introduction
Ever wondered how Oracle actually stores your data?

Let’s break it down — from big-picture storage like tablespaces, down to the tiniest data block.

Whether you’re using a CDB (Container Database) or PDB (Pluggable Database), these layers apply.

🧱 Storage: Two Levels – Physical and Logical

🔹 Physical Level – Where data is actually stored (on disk)
🔹 Logical Level – How Oracle organizes and manages that storage

Let’s look at each in detail ⬇️

🗂️ 🔸 Physical Storage Options

Oracle can store data files using three main mechanisms:

File System
➤ Traditional OS-based file structure
➤ Most Oracle DBs use this by default
Cluster File System
➤ Used in Oracle RAC (Real Application Clusters)
➤ Makes shared storage look like a single file system
➤ Multiple servers can access the same data
Oracle ASM (Automatic Storage Management)
➤ Oracle-managed storage
➤ Provides performance + reliability
➤ Reduces manual storage management

📌 Data files = The actual files that store your tables, indexes, etc.

🧠 🔹 Logical Storage Structures (Inside Oracle)

Layer	Description
Tablespace	A logical container for data. Can hold multiple segments. Each PDB has its own set of tablespaces.
Segment	Set of extents used by a schema object (like a table or index). One object = one segment.
Extent	Set of contiguous data blocks. More extents are added as needed.
Data Block	Smallest unit of logical storage. Usually 2KB, 4KB, 8KB, etc. Stored in data files.

🔁 Flow of Storage:

Tablespace → Segment → Extent → Data Block → Data File

📦 Tablespaces in Detail

✅ USERS tablespace
➤ Default location for user data

✅ Custom Tablespaces
➤ Can be created for separating system data, app-specific data, etc.

📌 One tablespace = many data files
📌 One data file = belongs to one tablespace only

🛠️ DBA Tip of the Day
Understanding how Oracle organizes storage helps you optimize performance and space usage.

When things go wrong — like fragmentation or storage alerts — this is where you start debugging.