Making and using oracle read-only tablespaces - oracle dba 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 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 and using oracle read-only tablespaces - oracle dba 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.
Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training Online.
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 rollback.
When there are transactions waiting to commit, the alter tablespace…read-only statement does not return immediately. It waits for all transactions to start 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.
[Related Article: Oracle DBA Interview Questions]
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.
Follow these steps to create a read-only tablespace on a cd-rom or worm (write once read many) devices.
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.
[Related Article: Oracle DBA Tutorial]
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:
[Related Article: Control File in Oracle]
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.