|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Making a tablespace read-only prevents write operations on the datafiles 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.
Note: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, as described in "Transporting Tablespaces Between Databases".
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 utilize 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.
The following topics are discussed in this section:
All tablespaces are initially created as read/write. Use the
READ ONLY clause in the
ALTER TABLESPACE statement to change a tablespace to read-only. You must have the
ALTER TABLESPACE or
MANAGE TABLESPACE system privilege.
Before you can make a tablespace read-only, the following conditions must be met.
The tablespace must be online. This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.
The tablespace must not currently be involved in an online backup, because the end of a backup updates the header file of all datafiles in the tablespace.
For better performance while accessing data in a read-only tablespace, you can 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 ensures that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for the database to check the status of the transactions that most recently modified the blocks.
The following statement makes the
flights tablespace read-only:
ALTER TABLESPACE flights READ ONLY;
You can issue the
ALTER TABLESPACE...READ ONLY statement while the database is processing transactions. After the statement is issued, the tablespace is put into a transitional read-only state. No transactions are allowed to make further changes (using DML statements) to the tablespace. If a transaction attempts further changes, it is terminated and rolled back. However, transactions that already made changes and that attempt no further changes are allowed to commit or roll back.
ALTER TABLESPACE...READ ONLY statement waits for the following transactions to either commit or roll back before returning: transactions that have pending or uncommitted changes to the tablespace and that were started before you issued the statement. If a transaction started before the statement remains active, but rolls back to a savepoint, rolling back its changes to the tablespace, then the statement no longer waits for this active transaction.
If you find it is taking a long time for the
ALTER TABLESPACE statement to complete, you can identify the transactions that are preventing the read-only state from taking effect. You can then notify the owners of those transactions and decide whether to terminate the transactions, if necessary.
The following example identifies the transaction entry for the
ALTER TABLESPACE...READ ONLY statement and displays its session address (
SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS AND SQL_TEXT LIKE 'alter tablespace%'; SQL_TEXT SADDR ---------------------------------------- -------- alter tablespace tbs1 read only 80034AF0
The start SCN of each active transaction is stored in the
V$TRANSACTION view. Displaying this view sorted by ascending start SCN lists the transactions in execution order. From the preceding example, you already know the session address of the transaction entry for the read-only statement, and you can now locate it in the
V$TRANSACTION view. All transactions with smaller start SCN, which indicates an earlier execution, can potentially hold up the quiesce and subsequent read-only state of the tablespace.
SELECT SES_ADDR, START_SCNB FROM V$TRANSACTION ORDER BY START_SCNB; SES_ADDR START_SCNB -------- ---------- 800352A0 3621 --> waiting on this txn 80035A50 3623 --> waiting on this txn 80034AF0 3628 --> this is the ALTER TABLESPACE statement 80037910 3629 --> don't care about this txn
You can now find the owners of the blocking transactions.
SELECT T.SES_ADDR, S.USERNAME, S.MACHINE FROM V$SESSION S, V$TRANSACTION T WHERE T.SES_ADDR = S.SADDR ORDER BY T.SES_ADDR SES_ADDR USERNAME MACHINE -------- -------------------- -------------------- 800352A0 DAVIDB DAVIDBLAP --> Contact this user 80035A50 MIKEL LAB61 --> Contact this user 80034AF0 DBA01 STEVEFLAP 80037910 NICKD NICKDLAP
After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary, because no changes can be made to it.
A prerequisite to making the tablespace read/write is that all of the datafiles in the tablespace, as well as the tablespace itself, must be online. Use the
DATAFILE...ONLINE clause of the
ALTER DATABASE statement to bring a datafile online. The
V$DATAFILE view lists the current status of datafiles.
The following statement makes the
flights tablespace writable:
ALTER TABLESPACE flights READ WRITE;
Making a read-only tablespace writable updates the control file entry for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.
Create a writable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
Alter the tablespace to make it read-only.
Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
Take the tablespace offline.
Rename the datafiles to coincide with the names of the datafiles you copied onto your WORM device. Use
ALTER TABLESPACE with the
RENAME DATAFILE clause. Renaming the datafiles changes their names in the control file.
Bring the tablespace back online.
When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage, you should consider setting the
READ_ONLY_OPEN_DELAYED initialization parameter to
TRUE. This speeds certain operations, primarily opening the database, by causing datafiles in read-only tablespaces to be accessed for the first time only when an attempt is made to read data stored within them.
READ_ONLY_OPEN_DELAYED=TRUE has the following side-effects:
A missing or bad read-only file is not detected at open time. It is only discovered when there is an attempt to access it.
ALTER SYSTEM CHECK DATAFILES does not check read-only files.
ALTER TABLESPACE...ONLINE and
DATAFILE...ONLINE do not check read-only files. They are checked only upon the first access.
V$DATAFILE_HEADER do not access read-only files. Read-only files are indicated in the results list with the error "
DELAYED OPEN", with zeroes for the values of other columns.
V$DATAFILE does not access read-only files. Read-only files have a size of "0" listed.
V$RECOVER_LOG does not access read-only files. Logs they could need for recovery are not added to the list.
ALTER DATABASE NOARCHIVELOG does not access read-only files.It proceeds even if there is a read-only file that requires recovery.
RECOVER DATABASE and
ALTER DATABASE OPEN RESETLOGS continue to access all read-only datafiles regardless of the parameter value. If you want to avoid accessing read-only files for these operations, those files should be taken offline.
If a backup control file is used, the read-only status of some files may be inaccurate. This can cause some of these operations to return unexpected results. Care should be taken in this situation.