Lesson 1

One of the biggest advances of relational databases is to free end users from the need to understand the physical storage of their information. Someone who is simply using the data in an Oracle database has no need to understand the way that Oracle stores its data. This does not mean that the database administrator, can afford to ignore the way Oracle stores information. Even though Oracle will manage its own physical storage, you can shape the way this space management occurs. To effectively manage an Oracle database, you must understand the way that an Oracle database stores information.
This module gives you a firm foundation for understanding how Oracle manages its storage space. You will learn:

1. The basic concepts of Oracle storage
2. The role of database blocks, extents, and segments
3. How to set storage characteristics for database objects and tablespaces
4. How to learn about storage characteristics from the data dictionary
5. How Oracle manages free space in database objects
6. How to learn about free space from the data dictionary
7. How fragmentation can affect your Oracle database and how to avoid the problems this can cause

Just as the storage of data is the foundation of your Oracle database, understanding and using storage characteristics of database objects and tablespaces are an important part of your work as a database administrator.

One of the key strengths of the relational model is how it separates the logical representation of data (tables, rows, columns) from the physical storage details. This allows users, whether they're running queries or building applications, to focus on what they want from the data rather than how it's stored on disk. It’s a huge leap from earlier systems like flat files or hierarchical databases, where physical structure dictated how you accessed data.
For a database administrator (DBA), though, the physical layer is critical. Oracle, for instance, organizes data into tablespaces, which are logical containers mapped to physical data files. Within those, you’ve got segments (like tables or indexes), extents (contiguous blocks of space), and data blocks (the smallest unit of storage). The DBA can influence performance and efficiency by tuning parameters like block size, extent allocation, or storage clauses in the CREATE TABLE statement—things like INITIAL, NEXT, or PCTFREE. These control how space is allocated and used over time, which can prevent fragmentation or wasted space. Oracle’s storage management has evolved too. With features like Automatic Storage Management (ASM) or Locally Managed Tablespaces (LMT), a lot of the grunt work is automated—freespace management, for example, doesn’t need micromanaging the way it did in older Dictionary Managed Tablespaces. But even with that, a good DBA still needs to know what’s happening under the hood: how indexes are stored (B-trees or bitmaps), how partitioning splits data across physical files, or how redo logs and undo tablespaces handle transactions. Ignoring this risks performance bottlenecks or even data loss if, say, a tablespace fills up unexpectedly. The end user gets to blissfully ignore all this, but for the DBA, mastering Oracle’s physical storage isn’t optional and it’s the difference between a system that hums and one that chokes.

To understand how space should be allocated within the database, you first have to know how the space is used within the database. In this module, you will see an overview of the Oracle database space usage functions.

1. When a database is created, it is divided into multiple logical sections called tablespaces.
2. The SYSTEM tablespace is the first tablespace created.
3. You can then create additional tablespaces to hold different types of data (such as tables, indexes, and rollback segments).
4. When a tablespace is created, datafiles are created to hold its data.
5. These files immediately allocate the space specified during their creation.
6. Each datafile can support only one tablespace.
7. A database can have multiple users, each of whom has a schema.

Each user's schema is a collection of logical database objects, such as tables and indexes, that refer to physical data structures that are stored in tablespaces. Objects from a user's schema may be stored in multiple tablespaces, and a single tablespace can contain objects from multiple schemas. When a database object (such as a table or index) is created, it is assigned to a tablespace via user defaults or specific instructions. A segment is created in that tablespace to hold the data associated with that object. The space that is allocated to the segment is never released until the segment is dropped, manually shrunk, or truncated. A segment is made up of sections called extents, which are contiguous sets of Oracle blocks.
Once the existing extents can no longer hold new data, the segment will obtain another extent. The extension process will continue until no more free space is available in the tablespace's datafiles or until an internal maximum number of extents per segment is reached. If a segment is composed of multiple extents, there is no guarantee that those extents will be contiguous. To review, databases have tablespaces, and tablespaces have datafiles. Within those datafiles, Oracle stores segments for database objects. Each segment can have multiple extents.