Tablespace Management and Encryption in Oracle AI Database 26ai

Tablespace management connects an application's storage requirements to the files, capacity, and protection mechanisms provided by Oracle. The DBA decides where application segments belong, how much they can grow, and what must be available to recover them. Oracle handles the allocation of individual extents and the reuse of space within segments according to the selected management methods.

This lesson separates those responsibilities, explains how row identifiers relate to stored data, and walks through creating an encrypted application tablespace. The examples target a pluggable database (PDB) in Oracle AI Database 26ai. They assume that the database and its Transparent Data Encryption (TDE) infrastructure have already been provisioned.

What Tablespace Management Controls

A tablespace is a logical storage container. Permanent tablespaces and undo tablespaces use physical datafiles; temporary tablespaces use tempfiles. A smallfile tablespace can contain multiple files, while a bigfile tablespace has a single file. Creating another tablespace establishes another logical management boundary, but does not necessarily establish a separate physical storage device.

Application tables, indexes, and other stored structures allocate space through segments. Some objects need multiple segments: a partitioned table, for example, does not place all of its data in one ordinary table segment. Views and stored program definitions should not be confused with application data segments simply because they are database objects.

Good tablespace design answers practical questions before an application runs out of space:

• Placement: Which application or class of data belongs here?
• Growth: What initial allocation, extension increment, and maximum file size are appropriate?
• Ownership: Which schemas may allocate space, and what quotas should apply?
• Availability: What application functions depend on this storage remaining accessible?
• Protection: Does the data require encryption, and can the necessary keys be recovered?

Consider an application that retains transaction details for several years. Separating active and historical data can support different maintenance schedules and storage policies. However, placing their files in the same saturated storage pool does not remove an I/O bottleneck. Logical organization and physical performance planning are related decisions that require separate evidence.

Keep application storage distinct from Oracle-managed storage. SYSTEM contains essential dictionary information, and SYSAUX supports database components. Undo supports transaction rollback and read consistency. Temporary space supports operations such as sorts that cannot be completed in memory. These areas have different operational rules and should not be treated as interchangeable destinations for user tables.

Tablespaces, Extents, and Segment Free Space

The storage hierarchy helps explain what Oracle manages automatically. A database block is a unit of database storage. An extent is a set of contiguous blocks allocated to a segment. A segment consists of extents holding data for a particular storage structure. A tablespace contains segments and supplies the files in which their extents reside.

For modern application tablespaces, use locally managed tablespaces (LMTs). Oracle tracks extent allocation with bitmaps in the tablespace's files. Dictionary-managed extent allocation belongs to legacy administration history; it is not the default design to teach for a new 26ai application.

Within a locally managed tablespace, AUTOALLOCATE lets Oracle select extent sizes, while UNIFORM uses a specified uniform extent size. These choices concern extent allocation. SEGMENT SPACE MANAGEMENT AUTO selects ASSM, which uses bitmaps to manage available space within eligible segments. LMT and ASSM therefore address different levels of allocation.

Use ASSM for the ordinary permanent application tablespace in this lesson. Do not extend that instruction indiscriminately to SYSTEM, temporary, or undo tablespaces. The next lesson examines locally managed tablespaces in more detail.

ROWIDs Locate Rows; Keys Identify Records

A row identifier helps Oracle locate a row. For an ordinary heap-organized table, an extended physical ROWID contains a data object identifier and information locating the file, block, and row slot. The encoding depends on storage characteristics, so a smallfile-specific decoding explanation should not be applied blindly to a bigfile tablespace.

The older restricted ROWID format is compatibility background. Extended ROWIDs are established Oracle functionality, not a feature newly introduced in 26ai. Their storage-relative addressing does not make them permanent identifiers that an application can safely carry between unrelated databases.

Index-organized tables use logical ROWIDs, which incorporate primary-key information and can include a physical guess to accelerate access. The UROWID data type can represent logical as well as physical row identifiers. This distinction matters when writing code that must accommodate different table organizations.

Use primary or unique keys for durable application identity. Row movement, rebuilding, and other reorganizations can change physical locators. A saved ROWID is therefore unsuitable as a permanent customer number or transaction identifier. Similarly, do not assume that exporting, importing, or moving data preserves every previously recorded locator.

What Tablespace Encryption Protects

TDE tablespace encryption protects stored data at rest. Oracle encrypts data as it is written to the encrypted tablespace and decrypts it for authorized database access. Applications normally continue issuing their existing SQL without implementing their own encryption and decryption calls.

Encryption does not change the meaning of a permission grant. A user authorized to query a table can still receive readable results. TDE therefore complements access controls, auditing, and network protection. It does not prevent an authorized account from exporting readable information, and it does not replace encryption of network connections.

Plan protection for each output separately. A SQL query spooled to a text file is outside the source tablespace's protection. Likewise, review Data Pump and RMAN encryption settings according to the operation rather than assuming every export or every part of a backup inherits the tablespace's encryption policy.

Oracle's 26ai encryption changes identify AES256 as the new default and introduce AES-XTS tablespace encryption. Default cipher-mode behavior depends on compatibility and configuration; the documented XTS default applies with COMPATIBLE at 23 or higher. Upgrading database software does not prove that existing tablespaces were rekeyed. Inspect their actual properties.

The example below requests AES256 explicitly. Algorithm and cipher mode are separate properties, and both should be verified. Do not infer that every supported AES key length uses XTS.

Prepare the Target PDB

Connect through the service for the intended PDB. Before creating storage, confirm that this is the container used by the application. A valid statement executed in the wrong container still creates the wrong administrative result.

In SQL*Plus or SQLcl, perform these checks. The SHOW lines are client commands; the SELECT is SQL.

SHOW CON_NAME
SHOW PARAMETER compatible
SHOW PARAMETER db_create_file_dest

SELECT con_id, status, wallet_type, keystore_mode
FROM   v$encryption_wallet
ORDER  BY con_id;

Access to administrative views requires appropriate privileges. Interpret wallet information for the target PDB and its united or isolated keystore arrangement. An open root keystore alone is not proof that the application PDB has an initialized master key. In particular, OPEN_NO_MASTER_KEY does not indicate readiness for this exercise.

Have the responsible administrator confirm the following prerequisites:

• The target PDB is open read-write, and the account has CREATE TABLESPACE.
• TDE is configured, with usable master-key material for this PDB.
• The installed edition or service supports the intended TDE configuration.
• The encryption policy permits creating the encrypted tablespace.
• DB_CREATE_FILE_DEST identifies an approved destination with sufficient space.
• The proposed tablespace name is unused.

Keystore creation, opening, backup, and master-key administration are separate privileged operations. They are not authorized merely by granting CREATE TABLESPACE. Follow the deployment's TDE procedure instead of running a generic wallet setup script against an existing database.

The TABLESPACE_ENCRYPTION parameter controls encryption policy. Oracle documents AUTO_ENABLE as the cloud default and MANUAL_ENABLE as the on-premises default. Check actual settings and service requirements. This static, CDB-scoped policy should not be presented as a setting to toggle inside the application PDB. The older ENCRYPT_NEW_TABLESPACES parameter is deprecated.

Create an Encrypted Application Tablespace

The following example uses Oracle Managed Files (OMF). Oracle chooses the physical filename using the configured destination. OMF can work with an appropriate filesystem or ASM destination; omitting a filename does not, by itself, mean that ASM is being used.

CREATE BIGFILE TABLESPACE secure_data
  DATAFILE SIZE 200M
  AUTOEXTEND ON NEXT 100M MAXSIZE 2G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO
  ENCRYPTION USING 'AES256' ENCRYPT;

These are laboratory sizes, not a production capacity recommendation. Each clause makes a specific decision:

• BIGFILE explicitly selects a single-datafile tablespace.
• SIZE 200M establishes its initial file allocation.
• AUTOEXTEND ON NEXT 100M MAXSIZE 2G permits bounded growth.
• EXTENT MANAGEMENT LOCAL selects local extent tracking.
• SEGMENT SPACE MANAGEMENT AUTO selects ASSM.
• ENCRYPTION USING 'AES256' ENCRYPT requests encryption with AES256.

A bigfile tablespace cannot receive a second datafile. Its growth is handled through the existing file. In a non-OMF installation, supply an administrator-selected, unused filename in the DATAFILE clause instead of copying an arbitrary path from another system.

The 2 GB maximum is a file-growth limit. It neither reserves that much underlying storage nor caps the combined consumption of all PDB tablespaces. Capacity planning must still consider other datafiles, temporary activity, and the storage platform. Autoextension can fail before its configured maximum if another limit is reached.

Allow a Schema to Use the Tablespace

Creating a tablespace does not give every schema permission to allocate unlimited storage there. For an existing APP_USER in the same PDB, an authorized DBA can assign a quota:

ALTER USER app_user QUOTA 100M ON secure_data;

This example assumes the account has CREATE SESSION and CREATE TABLE, and does not hold UNLIMITED TABLESPACE overriding the intended quota. The quota controls the schema's allocation in this tablespace; it does not change the datafile's maximum size.

Connect as APP_USER through the same PDB service, then create a small demonstration table. The primary-key index is explicitly placed in the same tablespace so the example does not depend on a different default index destination.

CREATE TABLE encryption_demo (
  demo_id NUMBER,
  note_text VARCHAR2(100),
  CONSTRAINT encryption_demo_pk PRIMARY KEY (demo_id)
    USING INDEX TABLESPACE secure_data
) TABLESPACE secure_data;

The table and its specified supporting index use encrypted storage when their segments are allocated. This operation does not move existing application objects. Review placement separately for other indexes, partitions, and LOB storage when applying the design to a real schema.

Verify the Result

Return to an account with access to the required administrative views in the target PDB. First inspect the tablespace definition:

SELECT tablespace_name, contents, bigfile,
       extent_management, segment_space_management, encrypted
FROM   dba_tablespaces
WHERE  tablespace_name = 'SECURE_DATA';

For this example, check for permanent contents, a bigfile definition, local extent management, automatic segment-space management, and ENCRYPTED = YES. These are expected properties, not captured execution results.

Next inspect the encryption state and cipher details:

SELECT t.con_id, t.name AS tablespace_name,
       e.encryptedts, e.encryptionalg, e.ciphermode, e.status
FROM   v$tablespace t
JOIN   v$encrypted_tablespaces e
       ON e.ts# = t.ts#
      AND e.con_id = t.con_id
WHERE  t.name = 'SECURE_DATA';

Both the tablespace number and container identifier participate in the join. This avoids treating tablespace numbers from different containers as the same object. Look for ENCRYPTEDTS = YES, the requested AES256 algorithm, the actual cipher mode, and the status.

NORMAL means that none of the view's listed transitional states is active; it does not independently prove encryption. Interpret it alongside the encryption flag. Oracle also notes that this view's information is meaningful for open containers and online datafiles. Missing or unexpected results require investigation of scope and file state, not an assumption that encryption succeeded.

Existing Tablespaces and Recoverable Keys

Existing tablespaces can be encrypted using supported online or offline conversion procedures. Moving every table into a newly encrypted tablespace is therefore not the only option. Choose the procedure for the database configuration and operational requirements.

Before conversion, account for auxiliary storage, workload impact, backup readiness, and available keys. Online conversion does not mean zero resource usage or zero operational risk. RAC and Data Guard deployments also require coordinated treatment of keys and database roles. Use Oracle's tablespace encryption conversion procedures for the detailed operation.

Recovery planning must include key material. Retain historical master keys needed by older backups, protect keystore backups, and test restoration with the required keys available. A successful datafile backup is insufficient evidence that encrypted data can be recovered in a replacement environment.

Applying the Lesson

When diagnosing a storage problem, identify the controlling layer first. A schema quota error is not repaired merely by increasing a file's maximum size. Available capacity in a storage pool does not prove that the user has allocation rights. Similarly, a correctly created tablespace does not prove that its encryption keys are recoverable.

A useful completion record includes the PDB, tablespace name, intended application owner, growth limits, encryption verification, and recovery dependencies. Keep this information with the deployment documentation so that the next administrator can distinguish deliberate limits from accidental configuration.

Next lesson: Examine locally managed tablespaces and how their extent-allocation choices affect storage administration.

Oracle Documentation

• Managing Tablespaces
• CREATE TABLESPACE syntax and restrictions
• TABLESPACE_ENCRYPTION policy
• V$ENCRYPTION_WALLET
• V$ENCRYPTED_TABLESPACES