Disclaimer: The author does take any responsibility for any loss, harm or damage occasioned by or otherwise associated with the information provided herein.

Riddles of Read-Only Tablespace

Read only tablespaces have been popular since ORACLE 7. They have found their niche in Date warehouses and other static environments. This article discusses about the benefits of the read-only tablespace, how to handle the read-only tablespace, and the possible pitfalls in using them.

OVERVIEW

The read-only tablespace is a tablespace that does not allow any updates or creations of new objects in the tablespace. The read-only tablespace can exist in both the regular disk drives and the read-only media like CD-ROMs.

The read-only tablespace has the following advantages over a regular tablespace:

1. Eliminates the need to perform backups. It just needs a single backup after making it read-only.

2. Recovery becomes easier. When the instance is started ORACLE will take advantage of the fact that read-only tablespace does not need any media recovery.

3. A read-only tablespace has a lesser over head than an updateable tablespace.

4. A read-only tablespace can be defined as an updateable tablespace at the instance level. A tablespace can be in an update mode for instance while it is in a read-only mode for the other instance.

5. The read-only tablespace can be transferred to a read-only media like CD-ROM or DVD-ROM which are easier to store and more economical than the regular disks.

6. Since there will not be any write operation against the data files of the read-only tablespace there will be lesser system IO.

In order to make a tablespace a read-only table space the following requirements need to be satisfied:

1. The tablespace to be converted must be quiet. There should not be any active transactions against the tablespace.

2. There should not be any rollback segments in the tablespace. Take them offline and drop them.

3. If you plan on storing the tablespace on to a read-only media it should be done prior to making the tablespace read-only. This can be accomplished by the following command.

Alter Tablespace My_Tablespace Rename Datafile

Old_file to Read_Only_File;

4. The tablespace must be online and must not be involved in a hot backup.

5. The compatible parameter in the init.ora must be set to 7.1 or higher.

The following are recommended prior to making a tablespace read-only:

1. You may want to compress all the data for a tablespace to be made as read-only tablespace by exporting it and importing it after redefining the tablespace parameters and setting PctFree=0 for the table so that space wastage can be minimized.

2. Make a list of all the tables that are there in the tablespace that needs to made as read-only. You can then either analyze all those tables or issue a query like Select count (*) from table for each of the tables. This is recommended by ORACLE documentation. The above query would force the table blocks to be read.

After making sure the above requirements are satisfied you can make the tablespace read-only by issuing the following commands.

Startup Restrict;

Alter Tablespace My_Tablespace Read Only;

Alter Database Open;

When you make the tablespace read-only, a checkpoint will be performed on all the datafiles of the read-only tablespace.

4. If you want to rename the datafiles of the tablespace it should be done prior to switching the tablespace into a read only mode.

The following are recommended after making a tablespace read-only:

1. Take a backup of the read-only tablespace. You do not have to backup the read-only tablespace any more.

2. Take a backup of the control file. The control file gets updated when the change from read write mode to read-only mode.

At times you may want to switch the mode of a read-only tablespace to read-write for various reasons. In order to make a read-only tablespace read-write, it is imperative for all the datafiles in the read-only tablespace to be online prior to making the switch. The read-only tablespace can be made as a read-write tablespace by the following command:

Alter Tablespace My_Space Read Write;

Again it is imperative to take a backup of the control file.

Things to watch out for

When a back up control file is used for media recovery you may encounter some error messages due to the read-only tablespace. The simplest solution could be is to take the data files of the read-only tablespace offline prior to the recovery.

Never make the tablespace that contains system owned objects, temporary segments, and rollback segments as a read-only tablespace.

You can drop objects from the read-only tablespace as it merely removes the pointers to the object in the data dictionary without making any entry to the read-only tablespace.

Conclusion

By carefully segregating the static data on to a read-only tablespace, the DBA can minimize the maintenance problems of static data repository and improve the performance of the database.