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 provides a way to protect 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 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.
Making a tablespace read-only:
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.
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:
Sql>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.
When there are transactions waiting to commit, the alter tablespace…read only statement does not return immediately. It waits for all transactions started, before you issued the alter tablespace statement to either commit or rollback.
This transitional read-only state only occurs if the value of the initialization parameter compatible is 8.1.0 or greater. If this parameter is set to a value less than 8.1.0, the alter tablespace …read only statement fails if any active transactions exist.
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 note its session address (saddr):
Sql>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.
Sql>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
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.
Making a read-only tablespace writable:
Use the read write keywords in the alter tablespace statement to change a tablespace to allow write operations. You must have the alter tablespace or manage tablespace system privilege.
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:
Sql>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.
Creating a read-only tablespace on a worm device:
Follow these steps to create a read-only tablespace on a cd-rom or worm (write once-read many) device.
1. Create a writable tablespace on another device. Create the objects that belong to the tablespace and insert your data.
2. Alter the tablespace to make it 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. Use alter tablespace with the rename datafile clause. Renaming the datafiles changes their names in the control file.
6. Bring the tablespace back online.
Delaying the opening of datafiles in read-only tablespaces:
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.
Setting read_only_open_delayed=true has the following side-effects:
Free Demo for Corporate & Online Trainings.