Database Design
Read Only tables
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Sunday, 21 March 2010 22:34
-
Last Updated on Saturday, 26 October 2013 20:43
-
Published on Sunday, 21 March 2010 22:34
-
Written by Guy Lambregts
-
Hits: 4036
Read Only Tables
See also Which DDL & DML is possible in read only databases, read only tablespaces and read only tables
The requirement to have some read only data in the Oracle database is as old as Methusalem. Prior to Oracle 11G a DBA could achieve this
1. by revoking the privileges to change table data ( DCL ). The restriction is that the owner --- and far too many applications use the owner for daily operations --- can still change the table data.
2. by moving a table --- or a table partition --- to a read only tablespace. The read only attribute of a tablespace has some nice advantages with respect to backup and recovery.
Oracle 11G comes with an enhancement : Read Only Tables, here is some basic gym
SQL> column read_only format a10
SQL> select table_name,read_only from user_tables;
TABLE_NAME READ_ONLY
T_PRECIOUSMETALS_PM NO
TABLE_TS1 NO
TABLE_TR1 NO
TABLE_TR2 NO
TABLE_TI1 NO
TABLE_TI2 NO
TABLE_PARENT_TI1 NO
TABLE_CHILD_TR1 NO
TABLE_TV1 NO
TABLE_TRC1 NO
TABLE_TV2 NO
T_COMP_NC NO
T_COMP_DC NO
T_COMP_AC NO
T_CHILD NO
T_PARENT NO
T_EXCHANGERATE_ER NO
17 rows selected.
SQL> alter table t_exchangerate_er read only;
Table altered.
SQL> truncate table t_exchangerate_er;
truncate table t_exchangerate_er
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "PM"."T_EXCHANGERATE_ER"
SQL> delete from t_exchangerate_er;
delete from t_exchangerate_er
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "PM"."T_EXCHANGERATE_ER"
SQL> alter table t_exchangerate_er read write;
Table altered.
When a table is read only the below listed operations are not possible
1. DML
2. Truncate
3. select for update
4. drop partitions
4. make column unusable
5. flashback table
When a table is read only the below listed operations are still possible
1. read operations
2. index maintenance
3. change storage attributes
4. drop
5. rename
6. move