Oracle 11g Read-Only Tables

Summary

Prior to 11g you could make an entire tablespace read-only which meant that you had to group all your tables that you wanted to be read-only into a common tablespace or set of tablespaces, or you had to create a trigger that would cause an error. 11G now allows a single table to be made read-only without affecting the other tables in the tablespace. This can help save the overhead of having to move tables to read-only tablespaces when you are read to make the table read-only. Not only can we place the table read-only but can change it back to read-write to allow insert and updates when needed.

The read-only designation for a table will stop all DML (truncate/insert/update/delete/etc) operations and certain DDL operations as well like ALTER TABLE to add/modify/rename/drop columns, ALTER TABLE drop/truncate/exchange (SUB)PARTITION, ALTER TABLE upgrade. Though keep in mind that some DDL can still be done against a table designated read-only like DROP TABLE, ALTER TABLE add/coalesce/merge/modify/move/rename/split (SUB)PARTITION, ALTER TABLE rename/move/add supplemental log/drop supplemental log/deallocate ununsed.
To identify if a table is read-only a new column has been added to dba_tables, user_tables and all_tables called READ_ONLY. This column will contain YES if the table is read-only and NO when the table is not read-only.

Unfortunately at this time Oracle does not allow the ability to just take a partition of a table and make it read-only, to make a partition of a table read-only the partition must be moved to a tablespace that can be marked read-only. One can only hope Oracle will help us out and provide that ability in future releases saving the overhead of move partitions or having to place partitions in separate tablespaces to get the partitions to be read-only.

Putting table into read-only:

alter table big_emp2 read only ;

Taking table out of read-only

alter table big_emp2 read write ;

11g (11.1.0.6 Windows) Test

Create the table for the test and load with dummy data.

SQL> -- create the table for the test
SQL> CREATE TABLE big_emp2
2 (EMPNO NUMBER(4) CONSTRAINT big_emp2_pk primary key disable,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2)
10 )
11 PARTITION BY RANGE (HIREDATE)
12 (PARTITION y1980 VALUES LESS THAN ( TO_DATE('01-JAN-1981','DD-MON-YYYY') ) TABLESPACE USERS,
13 PARTITION y1981 VALUES LESS THAN ( TO_DATE('01-JAN-1982','DD-MON-YYYY') ) TABLESPACE USERS,
14 PARTITION y1982 VALUES LESS THAN ( TO_DATE('01-JAN-1983','DD-MON-YYYY') ) TABLESPACE USERS,
15 PARTITION y1983 VALUES LESS THAN ( TO_DATE('01-JAN-1984','DD-MON-YYYY') ) TABLESPACE USERS,
16 PARTITION y1984 VALUES LESS THAN ( TO_DATE('01-JAN-1985','DD-MON-YYYY') ) TABLESPACE USERS,
17 PARTITION y1985 VALUES LESS THAN ( TO_DATE('01-JAN-1986','DD-MON-YYYY') ) TABLESPACE USERS,
18 PARTITION y1986 VALUES LESS THAN ( TO_DATE('01-JAN-1987','DD-MON-YYYY') ) TABLESPACE USERS,
19 PARTITION y1987 VALUES LESS THAN ( MAXVALUE ) TABLESPACE USERS
20 )
21 /

Table created.

SQL>
SQL> -- create the unique index for the primary key
SQL> create unique index beig_emp2_pk on big_emp2 (empno) ;

Index created.

SQL>
SQL> -- enable the primary key using the unique index
SQL> alter table big_emp2 enable primary key using index ;

Table altered.

SQL> -- load data
SQL> insert into big_emp2 select * from big_emp ;

9254 rows created.


Make the Table Read Only

SQL> alter table big_emp2 read only ;

Table altered.


Check that the table shows read-only

SQL> select table_name, read_only from user_tables where table_name = 'BIG_EMP2';

TABLE_NAME REA
------------------------------ ---
BIG_EMP2 YES


Attempt to do DML operations against the table

SQL> update big_emp2 set salary = salary + 200 ;
update big_emp2 set salary = salary + 200
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> insert into big_emp2 values (0,'My Name','MGR','1000','01-JAN-08',5000,1000,10) ;
insert into big_emp2 values (0,'My Name','MGR','1000','01-JAN-08',5000,1000,10)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> delete from big_emp2 ;
delete from big_emp2
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> truncate table big_emp2 ;
truncate table big_emp2
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"


Attempt to do DDL operations against the table

SQL> alter table big_emp2 add ssn varchar2(11) ;
alter table big_emp2 add ssn varchar2(11)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> ALTER TABLE big_emp2 drop partition y1980 ;
ALTER TABLE big_emp2 drop partition y1980
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"