Oracle Read Only TableSpaces

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.

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