Tablespace status-read only, read write
1. The primary purpose of the read-only tablespace is to eliminate the need for backup and recovery of most static data in the database. Oracle does not update read-only tablespaces that love your files, so these files can be stored in Read-Only media such as CD-ROM or worm drives.
2. Read-Only tablespaces are not designed to meet archiving requirements. The read-only tablespace cannot be modified. If you need to modify the records in the read-only tablespace, you must first set the tablespace to read/write. After the tablespace is updated, it can be reset to read-only.
3. Because the read-only tablespace cannot be modified, as long as it is not set to read/write, it does not need to be backed up repeatedly. In addition, if you need to recover the database, you do not need to recover the read-only tablespace because they have not been modified.
4. objects such as tables or indexes can be deleted from read-only tablespaces, but objects cannot be created or modified. You can execute the statement to modify the file description in the data dictionary, such as alter table... add or alter table... modify, but no new description information can be added, unless the tablespace is set to read/write.
5. Read-Only tablespace can be exported and imported to other databases. Since read-only tablespaces cannot be modified, they can be stored in CD-ROM or worm (one write-multiple reads) facilities.
6. read/write is used for initial creation of all tablespaces. You can use the read only clause to set the tablespace to read-only. The premise is that you must have system permissions for alter tablespace or manage tablespace.
Before using alter tablespace... read only, the following conditions must be met:
> The tablespace is online. This is to ensure that no undo information needs to be applied to the tablespace. NOTE: If it is offline, the Undo information will be stored in the system tablespace, And the Undo information will be applied when it is restored to online.
> The active undo or system tablespace cannot be modified.
> The tablespace cannot be in the currently in-progress online backup because the header information of all data files in the tablespace is updated at the end of the backup.
> To Improve the Performance of reading data from the read-only tablespace, You can query all data blocks in the table that accesses the tablespace before setting it to read-only. A simple query like select count (*) ensures the optimal efficiency of data blocks in the tablespace during subsequent access. This approach does not require the database to check the Transaction Status of recently modified data blocks.
7. You can execute the alter tablespace... read only statement when the database is processing the transaction. After the statement is executed, the tablespace is in the read-only transaction status. No transactions (DML operations) are allowed to be applied to tablespaces. If a transaction is attempted, the operation is terminated and rolled back. However, transactions that have made changes and do not make further changes are allowed to perform the commit or roll back operation.
If alter tablespace... before the read only statement is executed, a transaction has been executed, but rolled back to a storage point and rolled back his changes to the table space. Then alter tablespace... the read only statement does not wait for the transaction of this activity.
8. Transaction-level read-only status can be used only when the initialization parameter compatible is 8.1.0 or above. If the parameter value is less than 8.1.0 and an active transaction exists, the alter tablespace... read only statement fails.
9. If the execution time of the atler tablespace statement is too long, you need to find the transactions that prevent the read-only status from taking effect. The following statement finds the transaction entry and session address (saddr) for executing the alter tablespace... RAED only statement ):
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 starting SCN of each activity transaction is stored in the V $ Transaction View. The smaller the starting SCN, the earlier the operation. The more likely this statement will block subsequent changes to the read-only status.
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 use the following statement to find the user blocking the transaction:
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 David B David blap --> contact this user
80035a50 Mikel lab61 --> contact this user
80034af0 dba01 steveflap
80037910 nickd nickdlap
We recommend that you back up the tablespace immediately after you set it to read-only. As long as the tablespace remains in the read-only status, you do not need to back up again because there will be no updates to the table space.
10. Use the later tablespace... Read Write statement to restore the read-only tablespace, provided that it has the alter tablespace or manage tablespace permission.
When the tablespace is set to read/write, all data files and tablespaces in the tablespace must be online. You can use the alter database... datafile... online statement to set the data file to online. The V $ datafile view displays the current status of the data file.
To change the tablespace to writable mode, you need to update the control file to use the read-only version of the data file as the recovery start point.
11. Create a read-only tablespace in the worm device.
(1) create a writable tablespace. Create an object and insert data.
(2) set the tablespace to read-only.
(3) run the operating system command to copy the tablespace data file to the worm device.
(4) set the tablespace to offline.
(5) Rename the data file to comply with the naming rules for the data file copied to the worm device. Use the alter tablespace... rename datafile statement to modify the control file.
(6) restore the tablespace to the online status.
Lab:
1. Set the tablespace to read only.:
SQL> alter tablespace dcsopen_tbs read only;
Tablespace altered.
2. view the data file status:
SQL> SELECT FILE #, name, status from V $ datafile where file # = 11;
File # name status
------------------------------------------------------------------------------------
11/Oracle/oradata_petest/petest/dcsopen_tbs02.dbf online
3. log on to the read-only tablespace:
SQL> select * From test1;
T1id t1v
--------------------
1 T1
2 T2
3 T3
You can read the tables in the table.
SQL> insert into test1 values (12, 't12 ');
Insert into test1 values (12, 't12 ')
*
Error at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/Oracle/oradata_petest/petest/dcsopen_tbs01.dbf'
When a transaction is executed, the system prompts that the data file No. 7 cannot be modified.
