Database Design

Read Only tables

• 

User Rating:  / 0

PoorBest 

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: 4030

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