Lesson 5	READ ONLY tablespaces
Objective	Describe how READ ONLY tablespaces protect static data, support transport operations, and affect backup and storage management in Oracle 26ai.

READ ONLY Tablespaces in Oracle 26ai

A READ ONLY tablespace keeps stored data available for queries while preventing changes that require writes to its datafiles. In Oracle AI Database 26ai, this mode is useful for historical records, stable reference data, and preparation for conventional transportable tablespace operations.

The setting applies to a tablespace, a logical storage container backed by datafiles. It does not make the entire database read-only. Applications can continue using writable tablespaces, although a transaction that also needs to modify a segment in the read-only tablespace can fail.

Read-only mode is a storage-management decision with application consequences. Before enabling it, establish which data should remain unchanged, identify every affected segment, and decide how exceptions will be handled. A tablespace holding completed sales history is a better candidate than one containing both that history and today's orders.

Two Main Reasons to Use READ ONLY

Keep Stable Data Available Without Routine Updates

Completed accounting periods, archived transactions, and published reference datasets often remain valuable for queries long after their normal update period ends. A read-only tablespace allows the database to serve those queries while rejecting ordinary attempts to change the stored data.

For example, a reporting application might keep current records in a writable tablespace and closed years in HISTORY_TS. An accidental update against historical rows is then blocked even if the application account normally has UPDATE privileges. The application should handle that failure explicitly rather than repeatedly retrying the same operation.

Because the datafiles remain unchanged during the read-only period, a suitable backup strategy can reduce repeated backup work. This benefit depends on retaining usable backups and managing them correctly. Read-only files are still exposed to storage failure, deletion, and corruption.

Prepare a Conventional Transportable Tablespace Set

The conventional transportable tablespace workflow combines a metadata export with consistent copies of the source datafiles. Keeping the source tablespaces read-only during that export and copy window prevents their contents from changing while the transport package is prepared.

Changing the mode alone does not transfer anything to another database. The DBA must still validate the transport set, export metadata, transfer files, and integrate the set at the destination. Review the previous lesson on defining transportable tablespaces for that sequence. Advanced backup-based and incremental transport methods have their own procedures for reducing downtime.

READ ONLY, OFFLINE, and Other Read-Only Settings

Several Oracle settings restrict access, but their scope and purpose differ. Choose the setting that matches the intended administrative task.

Scope of common access restrictions
Setting	Meaning for this lesson
Tablespace READ ONLY	Queries remain possible; changes requiring writes to the tablespace's stored segments are blocked.
Tablespace OFFLINE	Its contents are unavailable for normal access. This is not a substitute for keeping historical data queryable.
Database or PDB opened read-only	Applies at a broader database or container scope, rather than to one selected user tablespace.
Object-level read-only controls	Apply to the selected object or supported object component. They are distinct from changing the state of its tablespace.

Permissions remain relevant in every case. Making a tablespace read-only does not grant SELECT access to users who lack it, and making it writable does not grant UPDATE privileges. Storage state and authorization answer different questions.

Which Operations Are Allowed?

The useful distinction is whether an operation must change the read-only datafiles. It is inaccurate to say that all DDL is prohibited or that every object becomes administratively immutable.

Operations involving a read-only tablespace
Operation	Expected behavior
SELECT from accessible objects	Allowed with the required permissions.
INSERT, UPDATE, DELETE, or MERGE affecting stored data	Blocked when the operation requires changes to segments in the read-only tablespace.
Create or rebuild a segment in that tablespace	Requires writable storage and cannot proceed there while it remains read-only.
Dictionary-only DDL	Some operations are possible. The exact statement and its storage requirements determine support.
Drop an existing table or index	Can remain possible for an authorized user, despite the read-only state.
Return the tablespace to READ WRITE	Possible for an authorized administrator when the prerequisites are satisfied.

Deleting rows and dropping a table are different operations. DELETE modifies table data, while DROP can change the database's metadata describing the object. Therefore, read-only mode should not be treated as protection against every destructive administrative action.

Similarly, do not assume that any ALTER TABLE statement is safe merely because it looks like a definition change. Some changes require segment work; others can be handled in the dictionary. Consult the documented rules for the particular operation rather than relying on a broad DDL exception.

Prepare a User Tablespace for the Change

The following example uses an existing permanent user tablespace named HISTORY_TS. Its contents have already been reviewed and approved for read-only use. Run the SQL in the intended PDB through an appropriately authorized administrative session.

The DBA needs ALTER TABLESPACE or MANAGE TABLESPACE privileges for the state change, plus authorized access to the catalog views used for inspection. In SQL*Plus, verify the current container before changing anything:

SHOW CON_NAME

SELECT tablespace_name, contents, status
FROM dba_tablespaces
WHERE tablespace_name = 'HISTORY_TS';

SELECT file_id, file_name, status, online_status
FROM dba_data_files
WHERE tablespace_name = 'HISTORY_TS'
ORDER BY file_id;

SHOW CON_NAME is a SQL*Plus command. The SELECT statements inspect the tablespace and its files. Confirm that the service and container are correct, especially when similar tablespace names exist in several PDBs.

The tablespace must be online and must not be in an active user-managed online backup. The SYSTEM, SYSAUX, and temporary tablespaces cannot be made READ ONLY. The active undo tablespace is also excluded. Use a permanent user tablespace whose data can actually stop changing.

Before proceeding, check application schedules for loads, corrections, index maintenance, and other writes. A dataset described as historical may still receive late adjustments. Decide how long the data must remain stable and who can approve a later return to writable mode.

Change to READ ONLY and Verify the Result

Issue the State Change

ALTER TABLESPACE history_ts READ ONLY;

The change can involve a transitional read-only state. Existing transactions can finish by committing or rolling back, but further DML against the tablespace is prevented, apart from rollback of existing changes. The ALTER statement can wait for transactions to finish; it is not necessarily an instantaneous operation.

Coordinate the transition with application owners. If it takes longer than expected, investigate the outstanding work and allow it to complete through the normal transaction controls. Repeatedly submitting the command or terminating sessions without understanding their work is not a sound default response.

Confirm the Final Status

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'HISTORY_TS';

After successful completion, the expected status is READ ONLY. Use this tablespace-level view to verify the mode. The STATUS column in DBA_DATA_FILES has a different meaning and should not be interpreted as a substitute for this check.

Run representative application queries to confirm that the intended data remains accessible. Verify scheduled reporting as well as interactive SQL, since service names, users, and access paths may differ. Demonstrating rejected writes is useful in a disposable training database; it is unnecessary to issue an arbitrary production update just to test the restriction.

Return the Tablespace to READ WRITE

A correction, a new load, or a changed business requirement may require writes again. For the conventional local-storage example, ensure the tablespace and all its datafiles are online and that the storage is writable, then run:

ALTER TABLESPACE history_ts READ WRITE;

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'HISTORY_TS';

The writable, online tablespace normally appears as ONLINE in DBA_TABLESPACES.STATUS. Do not expect that column to display the literal value READ WRITE.

Re-enabling writes changes the assumptions behind the read-only period. Coordinate the application change, confirm successful access, and review backup coverage for subsequent modifications. If the data will later be frozen again, plan another validation and backup step after the next read-only transition.

The Object Storage case discussed below has an additional requirement: datafiles must be moved back to suitable traditional storage before the data can be modified. Do not assume the local-storage command sequence alone handles that move.

Backup Savings Depend on Recoverable Backups

A practical policy is to take and retain a suitable backup after the read-only transition. The stored data is then stable, so repeatedly copying identical files may provide little benefit compared with maintaining reliable retained copies and validating recovery procedures.

That does not mean “back up once and forget.” Backup media can fail, a backup can expire under retention rules, or the records needed to locate it can become unavailable. Include read-only files in restore planning and periodically verify that the required copies are accessible.

RMAN backup optimization can skip identical files that are already backed up when its criteria are met. The decision also depends on factors such as the backup device and retention policy. A database backup job does not automatically skip every read-only file merely because of its tablespace mode.

The explicit RMAN option BACKUP DATABASE SKIP READONLY excludes read-only datafiles from that operation. It is useful only when the backup plan already covers those files appropriately. The option itself does not establish that a usable backup exists, so it should not be added casually to a recurring job.

Recovery requirements also depend on the backup selected and the history of the file. A suitable backup taken during an unchanged read-only period can reduce recovery work. A backup from an earlier writable period, or a later return to writable mode, changes what may be required. Retain the redo and recovery metadata needed by the complete database recovery plan.

For example, freezing an archive in January does not protect its only backup from being deleted in June. If its datafile fails in July, the DBA still needs an appropriate recoverable copy. The saving comes from managing unchanged data intelligently, not from eliminating the obligation to restore it.

Design Tablespaces Around Data Lifecycles

Consider an orders table partitioned by year. Keeping closed years in separate tablespaces can make their storage easier to manage. However, a partition boundary and a tablespace boundary are not automatically the same thing.

Inventory the related segments before changing a tablespace's mode. Table partitions, indexes, and LOB segments may reside in different locations. An operation on a writable table can still encounter a problem if it also needs to update an index or another related segment in read-only storage.

Keeping unrelated active objects out of the history tablespace reduces that risk. It also makes the maintenance plan easier to explain: a specific group of completed data is frozen, while the operational data remains writable. Review global indexes and shared application dependencies rather than assuming a year-based table partition is entirely isolated.

Late adjustments deserve an explicit policy. The business may choose to reopen the affected storage temporarily, record a correction elsewhere, or delay the freeze until the adjustment period closes. The DBA should implement the agreed data lifecycle rather than using the storage setting to decide business rules.

Optional: Read-Only Tablespaces in OCI Object Storage

Oracle's 26ai documentation describes moving read-only datafiles to Oracle OCI Object Storage while retaining SQL access to their tables and partitions. This provides another storage option for stable data. It is distinct from putting RMAN backup pieces in a bucket or querying exported files through external tables.

The documented procedure uses ALTER DATABASE MOVE DATAFILE with a complete supported object URI. Configuration includes network connectivity, the applicable TLS wallet and proxy settings, network access control entries, bucket permissions, and a database credential referenced by the PDB's DEFAULT_CREDENTIAL property.

For this specific capability, follow the documented OCI Object Storage support. Do not generalize the procedure to arbitrary S3 or Azure endpoints. Confirm the requirements of the installed release and deployment before planning the move.

SQL clients continue querying the database, and Oracle retrieves blocks from the remote datafiles. Transparent access does not guarantee the same latency or throughput as local storage. Assess representative queries, network reliability, and the cost of bringing data back if future changes become necessary.

A sensible sequence is to establish the read-only period first and observe whether late writes occur before moving files. If updates are needed after relocation, move the data back to appropriate writable traditional storage. Use the documented Object Storage procedure for deployment-specific configuration.

Protection and Performance: Set Accurate Expectations

Read-only mode provides a useful barrier against ordinary data modification, but it is not immutable storage or a complete retention mechanism. Authorized users may still perform supported drops, and administrators can restore write access. Access controls, auditing, backups, and any required retention controls must be designed separately.

It also does not eliminate all database redo or undo. Other tablespaces continue processing transactions, and administrative metadata changes can generate logging. Likewise, read-only queries still depend on execution plans, statistics, memory, storage, and the workload. Select this mode because the data should stop changing, not because every query is expected to become faster.

Operational Checklist

Identify the PDB, permanent user tablespace, affected segments, and application owners.
Confirm that the data can stop changing and that the state-change prerequisites are met.
Coordinate active transactions, issue READ ONLY, and verify the completed status.
Check representative queries and retain appropriate recoverable backups.
Document how future corrections and any return to READ WRITE will be handled.
For transport or Object Storage, follow the additional procedure for that operation.

As a final check, ask whether the requirement is to prevent ordinary updates, preserve a recoverable copy, or enforce long-term immutability. READ ONLY addresses the first requirement directly. The other requirements need their own controls and validation.

Oracle Documentation

Read-Only Tablespaces - Quiz

Test your understanding of tablespace status, permitted operations, and backup considerations in the Read-Only Tablespaces - Quiz.

Read Only Tablespace - Quiz

Click the Quiz link below to test your knowledge about tablespace concepts.
Read Only Tablespace - Quiz