Oracle7 Server Administrator's Guide
Making a Tablespace Read-Only
This section describes issues related to making tablespaces read-only, and includes the following topics:
Making a tablespace read-only prevents further write operations on the datafiles in the tablespace. After making the tablespace read-only, you should back it up.
Use the SQL command ALTER TABLESPACE to change a tablespace to read-only. You must have the ALTER TABLESPACE system privilege to make a tablespace read-only. The following statement makes the FLIGHTS tablespace read-only:
ALTER TABLESPACE flights READ ONLY
After a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL command ALTER DATABASE RENAME.
A read-only tablespace is neither online nor offline. Issuing the ALTER TABLESPACE command with the ONLINE or OFFLINE option does not change the read-only state of the tablespace; rather, it causes all of the datafiles in the tablespace to be brought online or offline.
Prerequisites
Before you can make a tablespace read-only, the following conditions must be met. It may be easiest to meet these restrictions by performing this function in restricted mode, so that only users with the RESTRICTED SESSION system privilege can be logged on.
- The tablespace must be online.
- There must not be any active transactions in the entire database.
This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.
- The tablespace must not contain any active rollback segments.
For this reason, the SYSTEM tablespace can never be made read-only, since it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, it is recommended that you drop the rollback segments before you make a tablespace read-only.
- The tablespace must not currently be involved in an online backup, since the end of a backup updates the header file of all datafiles in the tablespace.
- The COMPATIBLE initialization parameter must be set to 7.1.0 or greater.
For better performance while accessing data in a read-only tablespace, you might want to issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table will ensure that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for Oracle7 to check the status of the transactions that most recently modified the blocks.
Warning: You cannot rename or resize datafiles belonging to a read-only tablespace.
See Also: For more information about read-only tablespaces, see .
Making a Read-Only Tablespace Writeable
Whenever you create a tablespace, it is both readable and writeable. To change a read-only tablespace back to a read-write tablespace, use the SQL command ALTER TABLESPACE. You must have the ALTER TABLESPACE system privilege to change a read-only tablespace to a read-write tablespace. The following command makes the FLIGHTS tablespace writeable:
ALTER TABLESPACE flights READ WRITE;
Making a read-only tablespace writeable updates the control file for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.
Prerequisites
To issue this command, all of the datafiles in the tablespace must be online. Use the DATAFILE ONLINE option of the ALTER DATABASE command to bring a datafile online. The V$DATAFILE view lists the current status of a datafile.
Creating a Read-Only Tablespace on a WORM Device
You may wish to create a read-only tablespace on a WORM (Write Once Read Many) device when you have read-only files that do not require updating.
To Create a Read-Only Tablespace on a WORM Device
1. Create a writeable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
2. Issue the ALTER TABLESPACE command with the READ ONLY option to change the tablespace to read-only.
3. Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
4. Take the tablespace offline.
5. Rename the datafiles to coincide with the names of the datafiles you copied onto your WORM device. Renaming the datafiles changes their names in the control file.
6. Bring the tablespace online.