Introduction to Tablespace Management in Oracle AI Database 26ai

Tablespace management connects an application's storage requirements to the database structures and physical capacity that support them. A DBA needs to know where objects allocate space, which users may allocate it, how files can grow, and what evidence indicates that capacity is running short. Creating a tablespace is the beginning of that responsibility, not its completion.

This module introduces those decisions for Oracle AI Database 26ai. Consider a sales application with transaction tables, indexes, reports, and historical records. Its permanent data, temporary working space, and undo requirements serve different purposes. Managing them effectively starts with understanding those differences and identifying the application PDB in which the work belongs.

Learning Objectives

• Explain the relationship between tablespaces, files, segments, extents, and blocks.
• Identify the correct container and distinguish permanent, temporary, and undo storage.
• Compare bigfile and smallfile tablespaces and their growth options.
• Separate schema quotas, file limits, PDB storage constraints, and physical capacity.
• Use documented views to inspect configuration and interpret space metrics.
• Recognize the prerequisites for encryption, maintenance, transport, and lifecycle policies.

Understand the Logical and Physical Storage Model

A tablespace is a logical storage container. Permanent tablespaces use physical datafiles, while temporary tablespaces use tempfiles. Within the logical model, a segment holds storage for an object or part of an object, such as a table, an index, or a partition. A segment consists of extents, and each extent consists of database blocks.

A schema and a tablespace are different concepts. A schema owns database objects; a tablespace supplies storage. One schema can allocate objects in several tablespaces, and several schemas can allocate objects in the same tablespace when their privileges and quotas permit. A tablespace name does not establish who owns every object stored there.

Two bitmap mechanisms address different allocation tasks. Locally managed tablespaces track extent allocation using local bitmaps. Automatic segment space management, or ASSM, tracks free space within eligible segments. These established mechanisms remain relevant in 26ai. They do not replace object metadata in the data dictionary or eliminate every possible source of contention.

For ordinary permanent application tablespaces, local extent management and ASSM are common choices. Temporary and undo tablespaces have different purposes and rules, so a permanent-tablespace template should not be copied into every storage operation.

Start in the Correct Container

Oracle's multitenant architecture distinguishes the container database (CDB) from its pluggable databases (PDBs). Application storage work generally belongs in the intended application PDB. The CDB root also has storage that requires administration, but a query against the root does not automatically describe the application PDB you meant to inspect.

Connect through the intended PDB service and verify the session context. In SQL*Plus, this read-only command provides a simple check:

SHOW CON_NAME

Compare the displayed container with the environment plan. Switching containers through ALTER SESSION SET CONTAINER requires an appropriately privileged common user; it is not a universal step for every locally connected administrator. For a root-wide inventory, retain container identifiers when relating rows from container-aware views.

Also identify the deployment model. A self-managed database may expose filesystem or Oracle Automatic Storage Management (ASM) locations directly. A managed cloud service may provide different controls and restrictions. Use the service's supported storage procedures rather than assuming that every database permits arbitrary file creation or movement.

Choose the Tablespace Purpose and File Model

Keep ordinary application objects out of SYSTEM and SYSAUX. Monitor component storage through the appropriate supported interfaces rather than deleting unfamiliar system-owned objects to reclaim space.

A bigfile tablespace contains one datafile or tempfile. A smallfile tablespace can contain multiple files. Oracle documents bigfile defaults for databases created with 26ai DBCA templates, but existing and custom-created environments must still be inspected. Specify the file model explicitly in teaching examples and check DBA_TABLESPACES.BIGFILE in the actual database.

The file model affects maintenance. A bigfile tablespace grows through its single file; you cannot add a second datafile. A smallfile tablespace can grow by extending existing files or adding another file within applicable limits. Choose according to the storage architecture, operating procedures, and recovery requirements rather than assuming that either model improves every workload.

Separate tablespaces for data and indexes can support administrative policies. They do not automatically provide physical I/O separation when both use the same underlying storage. Establish the operational reason for a layout and measure the relevant constraint.

Create a Small, Explicit Test Example

The following example assumes a self-managed nonproduction PDB, appropriate creation privileges, sufficient capacity, and an unused name. It also assumes an approved Oracle Managed Files (OMF) destination has already been configured through DB_CREATE_FILE_DEST. Omitting the filename relies on that configuration; it does not mean Oracle can create a file without a valid destination.

CREATE BIGFILE TABLESPACE ts_app
  DATAFILE SIZE 1G
  AUTOEXTEND ON NEXT 128M MAXSIZE 4G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

This creates a permanent application tablespace with an initial file size of 1G, automatic growth increments of 128M, and a configured autoextension ceiling of 4G. These are illustrative sizes, not production recommendations. The statement does not explicitly configure encryption; encryption behavior also depends on the environment's settings. If policy requires encryption, establish and verify the supported TDE configuration before creating real application storage.

If additional allocated capacity is later required and available, the bigfile tablespace can be resized:

ALTER TABLESPACE ts_app RESIZE 2G;

The requested size is the new total, not an additional 2G. Do not append an ADD DATAFILE command to this example. Adding files requires a separate smallfile tablespace. Reducing a file is also not the reverse of every growth operation: allocated blocks can prevent a reduction, and resizing does not automatically relocate them.

After creation, inspect the tablespace and its file. Confirm the intended container, file model, destination, initial allocation, and growth settings. Retain the creation record so a later capacity investigation starts from known configuration rather than assumptions about the original template.

Separate Quotas from File and Storage Limits

For an existing application user in the same PDB, an authorized administrator can assign a quota:

ALTER USER app_user QUOTA 512M ON ts_app;

The quota does not grant permission to create tables, and it is not a limit on every resource the application consumes. Temporary work, undo, and allocations by other users require separate consideration. Review effective privileges: UNLIMITED TABLESPACE can override explicit tablespace quotas.

Suppose an insert fails because a segment cannot extend. Determine whether the user reached a quota, a file reached its growth limit, a PDB storage constraint applies, or the storage system lacks space. These conditions require different remedies. Increasing a quota cannot manufacture storage, and enabling autoextension cannot guarantee that space will remain available.

Inspect Configuration and Interpret Capacity Correctly

Begin with the actual tablespace attributes. Run the following query in the intended PDB with an account permitted to read the dictionary view:

SELECT tablespace_name, contents, status, bigfile,
       block_size, extent_management, segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

The results distinguish storage purpose, file model, block size, and allocation mechanisms. They describe configuration, not remaining physical disk space. Reading a dictionary view also does not establish that the account may perform every related maintenance operation.

For permanent tablespace usage, use documented metrics and the actual tablespace block size:

SELECT m.tablespace_name,
       ROUND(m.used_space * t.block_size / POWER(1024, 2), 1)
         AS used_mib,
       ROUND(m.tablespace_size * t.block_size / POWER(1024, 2), 1)
         AS effective_capacity_mib,
       ROUND(m.used_percent, 1) AS used_percent
FROM dba_tablespace_usage_metrics m
JOIN dba_tablespaces t
  ON t.tablespace_name = m.tablespace_name
WHERE t.contents = 'PERMANENT'
ORDER BY m.used_percent DESC;

USED_SPACE and TABLESPACE_SIZE are measured in blocks. Multiplication by the tablespace block size and division by 1024 squared produces mebibytes (MiB). A hardcoded 8 KB conversion can misreport tablespaces using a different block size.

The capacity denominator needs care. With autoextension, TABLESPACE_SIZE reflects potential growth constrained by relevant limits and available underlying storage, including applicable PDB storage limits. It is not simply the sum of bytes already allocated to files. It also does not reserve that potential storage exclusively for this tablespace.

Several tablespaces may depend on the same remaining storage. Do not sum their potential capacities and treat the result as guaranteed free space. Inspect permanent files through DBA_DATA_FILES, including BYTES, AUTOEXTENSIBLE, and MAXBYTES, and compare these with the storage system's measurements.

Temporary-space views such as DBA_TEMP_FILES and DBA_TEMP_FREE_SPACE answer different questions. The permanent-only query above is not a complete diagnosis of temporary-space pressure or undo behavior. Select the view according to the resource being investigated.

Plan for Growth and the Time Needed to Respond

Capacity planning combines current usage with its rate of change. Record measurements over time and account for upcoming loads, retention changes, index builds, and maintenance that may require extra space. An otherwise adequate database can encounter a shortage during a large operation even when ordinary daily growth is modest.

Choose alerts according to remaining capacity, growth rate, and the time required to investigate and provision storage. A universal sequence of percentage thresholds cannot capture those differences. A rapidly growing small tablespace may need attention sooner than a large, stable one with a similar percentage used.

For the sales application, monitor transaction growth and peak reporting demand separately. If reports compete for CPU, Database Resource Manager may be relevant. If a segment cannot allocate another extent, workload CPU shares are not the remedy. OCI Resource Manager is a separate infrastructure-automation service, not another name for database storage allocation.

Plan Temporary Space and Undo Deliberately

Temporary storage supports working data for eligible operations that need it. Estimate peak concurrent demand rather than sizing solely from one report executed alone. Temporary tablespace groups are optional configuration tools; membership, assignment, and the default temporary target are separate parts of their use. Adding a tablespace to a group does not automatically change every user's assignment or create additional physical capacity.

Undo supports rollback and consistent reads. Its requirements depend on transaction activity and the duration of queries and other features that need older versions of data. Retention settings must be considered together with actual capacity and workload. A desired retention interval is not, by itself, proof that every required version will remain available.

Local undo is part of the CDB's multitenant undo configuration. Check the actual mode and deployment before planning PDB storage or operations; do not treat it as an independent preference each PDB can switch casually. Later procedures should specify the required scope and privileges explicitly.

Prepare for Maintenance, Encryption, and Transport

Distinguish moving a datafile from moving a table segment. A file relocation changes where a database file resides; a segment move changes an object's storage arrangement. Neither is synonymous with ASM disk-group rebalancing. Each operation has different restrictions, capacity requirements, and verification steps.

Supported online operations can reduce disruption, but they can still consume resources or encounter waits. Check concurrent workload, required working space, associated objects, and recovery arrangements. Measure a representative operation before assuming it will fit the production window.

Transparent Data Encryption (TDE) protects stored data under its configured coverage. It depends on accessible keys and does not replace database authorization. Review associated sensitive storage, including indexes and LOBs, rather than declaring an entire application protected after encrypting one tablespace. Coordinate key custody and recovery with database backup and restore planning.

Conventional tablespace transport combines eligible datafiles with metadata. Validate containment, source and destination compatibility, encryption requirements, and any required endian conversion. Read-only access can be part of the conventional workflow, but it is not a substitute for a backup or an archival retention policy. A standalone RMAN conversion command does not describe the complete transport process.

Preview Information Lifecycle Management

Information Lifecycle Management (ILM) relates storage choices to the changing value and use of data. Heat Map records activity that can inform supported policies. Automatic Data Optimization (ADO) can automate eligible compression or movement according to configured conditions.

For example, older sales records may have different access patterns from current transactions. Before choosing a policy, verify object support, Heat Map configuration, storage destinations, resource impact, and applicable licensing. Data age alone does not establish that compression or movement will help the application.

Keep policy automation observable. Decide how to confirm that the intended action occurred and whether query behavior and recovery remain acceptable afterward. An introductory policy fragment without these prerequisites is not a complete lifecycle-management procedure.

Your Starting Checklist

1. Confirm the database, PDB, deployment model, and administrative privileges.
2. Inventory tablespace purposes, file models, block sizes, and current growth settings.
3. Compare schema quotas and storage limits with available physical capacity.
4. Define growth expectations and alerts that allow time to respond.
5. Verify encryption requirements and recoverable key material.
6. Plan temporary and undo capacity for representative concurrent work.
7. Rehearse maintenance and verify both database state and application behavior.

The lessons that follow develop these storage-management topics in more detail. Carry forward the distinction between logical allocation, file growth, and physical capacity. Reliable tablespace administration depends on knowing which constraint applies and verifying that a change addresses it.

Oracle Documentation

• Managing Tablespaces
• CREATE TABLESPACE and ALTER TABLESPACE
• Using Oracle Managed Files
• DBA_TABLESPACE_USAGE_METRICS
• DBA_TABLESPACES
• Managing Time-Based Information