|
|
| Re: drop table in read only tablespace [message #158110 is a reply to message #158037] |
Wed, 08 February 2006 09:26   |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
| Quote: |
READ ONLY | READ WRITE
Specify READ ONLY to place the tablespace in transition read-only mode. In this state, existing transactions can complete (commit or roll back), but no further DML operations are allowed to the tablespace except for rollback of existing transactions that previously modified blocks in the tablespace. You cannot make the SYSAUX tablespace READ ONLY.
When 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 statement ALTER DATABASE ... RENAME.
|
| Quote: |
Using Read-Only Tablespaces
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.
|
Further information is available in the administrators guide in the chapter dedicated to tablespaces.
And while you can drop tables, you can't create them, or modify existing ones:
MYDBA > create tablespace ro_tablespace
2 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ro_tablespace.dbf' size 10m
3 ;
Tablespace created.
MYDBA > create table read_only_table (a number) tablespace ro_tablespace;
Table created.
MYDBA > alter tablespace ro_tablespace read only;
Tablespace altered.
MYDBA > drop table read_only_table;
Table dropped.
MYDBA > create table read_only_table2 (a number) tablespace ro_tablespace;
create table read_only_table2 (a number) tablespace ro_tablespace
*
ERROR at line 1:
ORA-01647: tablespace 'RO_TABLESPACE' is read only, cannot allocate space in it
MYDBA > alter tablespace ro_tablespace read write;
Tablespace altered.
MYDBA > create table read_only_table2 (a number) tablespace ro_tablespace;
Table created.
MYDBA > insert into read_only_table2 values (1);
1 row created.
MYDBA > commit;
Commit complete.
MYDBA > alter tablespace ro_tablespace read only;
Tablespace altered.
MYDBA > insert into read_only_table2 values (2);
insert into read_only_table2 values (2)
*
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RO_TABLESPACE.DBF'
As to why...well I don't know. Documented behavior, because that is the way it is intended to work.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
