Purpose and function of Oracle Read Only TableSpaces

In Oracle 19c, READ ONLY tablespaces serve a specific purpose in database management. They allow for optimization, security, and better resource utilization in scenarios where data does not need to be modified. Below is an overview of the purpose and functionality of READ ONLY tablespaces:
Purpose of READ ONLY Tablespaces

1. Data Integrity and Protection:
   • READ ONLY tablespaces ensure that the data within them cannot be modified. This is especially useful for storing historical or reference data that should remain static.
2. Reduced Backup Overhead:
   • Since the data in a READ ONLY tablespace cannot change, it does not need to be included in frequent backups after the initial backup. This reduces the time and resources required for backup operations.
3. Data Distribution:
   • READ ONLY tablespaces are commonly used in environments where data is shared or distributed across multiple databases, such as in a data warehouse or replication setup.
4. Improved Performance:
   • READ ONLY tablespaces eliminate the need for redo log generation and undo space allocation for operations on those tablespaces, as no data changes are allowed.
5. Security:
   • By marking a tablespace as READ ONLY, unauthorized or accidental updates to the data are prevented, enhancing security.

Functionality of READ ONLY Tablespaces

1. Setting a Tablespace to READ ONLY:
   • You can set a tablespace to READ ONLY mode with the following SQL command:

     ALTER TABLESPACE tablespace_name READ ONLY;
             

2. Accessing Data:
   • All users with appropriate permissions can query the data in a READ ONLY tablespace, but no DML (Data Manipulation Language) or DDL (Data Definition Language) operations are allowed on the objects within it.
3. Restrictions on READ ONLY Tablespaces:
   • No new objects (e.g., tables, indexes) can be created in the tablespace.
   • Existing objects in the tablespace cannot be modified or deleted.
   • Temporary segments cannot be created in the tablespace.
4. Reverting to READ WRITE:
   • To allow modifications, the tablespace can be set back to READ WRITE mode using:

     ALTER TABLESPACE tablespace_name READ WRITE;
             

5. Use in Transportable Tablespaces:
   • READ ONLY tablespaces are a prerequisite for transportable tablespaces, which allow moving tablespaces between databases with minimal overhead.
6. Data Loading:
   • You can load data into the tablespace while it is in READ WRITE mode and then switch it to READ ONLY once data loading is complete.

Use Cases for READ ONLY Tablespaces

1. Archiving Historical Data:
   • Ideal for storing data that is not expected to change, such as historical transaction records or archived reports.
2. Data Warehousing:
   • Useful for partitioned data in a data warehouse where older partitions are set to READ ONLY to prevent modifications.
3. Disaster Recovery:
   • Reduces recovery time in case of failure since READ ONLY tablespaces do not need redo log recovery.
4. Transportable Tablespaces:
   • Enables easier sharing of large datasets between different Oracle databases without exporting and importing data.
5. Regulatory Compliance:
   • Ensures the immutability of sensitive data to comply with data retention policies and regulatory requirements.

Benefits of READ ONLY Tablespaces

• Enhanced data stability and protection.
• Reduced backup frequency for unchanged data.
• Lower storage overhead due to no redo or undo logging.
• Easier data sharing across systems or environments.
• Improved query performance due to reduced contention on resources.

In summary, READ ONLY tablespaces in Oracle 19c are a powerful feature to optimize resource usage, enhance data security, and improve database performance when working with static or archived data.

There are two reasons to make a tablespace into a READ ONLY tablespace:

1. To store information that does not change. Data files for this kind of tablespace can reside on static media such as a CD-ROM.
2. To prepare a tablespace for transportation. You must place a tablespace in READ ONLY status prior to transporting it to another database.

All tablespaces are created in READ WRITE status. To change it to READ ONLY status, use this command:

ALTER TABLESPACE tablespacename READ ONLY;

Oracle has instituted two important changes in the timing of the command to change the tablespace to READ ONLY status. Prior to Oracle, all activity on the database had to complete before this command would execute. Oracle requires that only the activity on the tablespace must be complete. The second change is that Oracle places the tablespace in temporary READ ONLY status as soon as the command is issued. (This means no new changes can be initiated once the command has been issued.) These two changes make it faster and easier to place a tablespace in READ ONLY status.

A tablespace can be put into read-only mode. This prevents any data stored in it from being updated. Making a tablespace read-only prevents write operations on the data files in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level. Making a tablespace read-only cannot in itself be used to satisfy archiving or data publishing requirements, because the tablespace can only be brought online in the database in which it was created. However, you can meet such requirements by using the transportable tablespace feature. You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to use the new description until the tablespace is made read/write. Read-only tablespaces can be transported to other databases. And, since read-only tablespaces can never be updated, they can reside on CD-ROM or WORM (Write Once-Read Many) devices.

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