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:
- The basic concepts of Oracle storage
- The role of database blocks, extents, and segments
- How to set storage characteristics for database objects and tablespaces
- How to learn about storage characteristics from the data dictionary
- How Oracle manages free space in database objects
- How to learn about free space from the data dictionary
- 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.
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.
- When a database is created, it is divided into multiple logical sections called tablespaces.
- The SYSTEM tablespace is the first tablespace created.
- You can then create additional tablespaces to hold different types of data (such as tables, indexes, and rollback segments).
- When a tablespace is created, datafiles are created to hold its data.
- These files immediately allocate the space specified during their creation.
- Each datafile can support only one tablespace.
- 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.