Table Space Management   «Prev  Next»

Lesson 5 READ ONLY tablespaces
Objective Describe purpose and function of READ ONLY tablespaces.

Purpose and function of Oracle Read Only TableSpaces

In Oracle databases, a "read-only tablespace" serves a specific and important function in database management and security. The primary purpose of setting a tablespace to read-only mode is to protect the data it contains from any form of modification. This functionality is crucial for several reasons:
  1. Data Integrity and Protection: By making a tablespace read-only, an Oracle Database Administrator (DBA) ensures that the data within that tablespace cannot be altered. This is particularly important for historical data that must remain unchanged for compliance, audit, or data consistency reasons.
  2. Backup and Recovery Optimization: Read-only tablespaces can simplify backup and recovery processes. Since the data in these tablespaces does not change, it's often unnecessary to include them in regular backups after the initial backup, thus saving on backup storage space and reducing the time and resources required for backup operations.
  3. Performance Improvement: Access to read-only tablespaces can be faster compared to read-write tablespaces, as Oracle can optimize read operations knowing that the data will not change. This can lead to performance improvements, especially for large-scale queries or reports that access historical data.
  4. Preventing Accidental Changes: In a dynamic database environment, there is always a risk of accidental data modification. By setting certain tablespaces to read-only, a DBA can prevent inadvertent changes to critical data sets, thus safeguarding the integrity of the data.
  5. Support for Upgrade and Migration Processes: During database upgrades or migrations, setting tablespaces to read-only can be a useful strategy to ensure that data remains consistent and untouched during the process. This ensures that a reliable copy of the data is maintained, which can be vital for rollback procedures if needed.
  6. Facilitating Data Sharing: Read-only tablespaces can be used to share static data among multiple databases or applications without the risk of this data being modified. This is particularly useful in distributed database environments or data warehousing scenarios.

In conclusion, the use of read-only tablespaces in Oracle databases is a key tool for DBAs to ensure data integrity, optimize database performance, and implement robust data management strategies.

Two reasons to make tablespace a READ ONLY

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:


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.

About Read-Only Tablespaces

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.

Read Only Tablespace - Quiz

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