11G Example "Placing a Table in Read-Only Mode"

You can place a table in read-only mode with the ALTER TABLE...READ ONLY statement, and return it to read/write mode with the ALTER TABLE...READ WRITE statement.

To place a table in read-only mode, you must have the ALTER TABLE privilege on the table or the ALTER ANY TABLE privilege. In addition, the COMPATIBILE initialization parameter must be set to 11.1.0 or greater.

When a table is in read-only mode, operations that attempt to modify table data are disallowed. The following operations are not permitted on a read-only table:

- All DML operations on the table or any of its partitions
- TRUNCATE TABLE
- SELECT FOR UPDATE
- ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
- ALTER TABLE SET COLUMN UNUSED
- ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
- ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents
- Online redefinition
- FLASHBACK TABLE

The following operations are permitted on a read-only table:

- SELECT
- CREATE/ALTER/DROP INDEX
- ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
- ALTER TABLE for physical property changes
- ALTER TABLE DROP UNUSED COLUMNS
- ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITION
- ALTER TABLE MOVE
- ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK- RENAME TABLE and ALTER TABLE RENAME TO
- DROP TABLE
- ALTER TABLE DEALLOCATE UNUSED
- ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

Example:
SQL> select * from res;
RES_ID TIME HOTEL_ID
---------- --------- ----------
100002 20-FEB-08 13

SQL> delete from res;
1 row deleted.

SQL> select * from res;
no rows selected

SQL> rollback;
Rollback complete.

SQL> alter table res read only;
Table altered.

SQL> delete from res;
delete from res *ERROR at line 1:ORA-12081: update operation not allowed on table "SURACHART"."RES"

SQL> alter table res enable row movement;
Table altered.

SQL> desc res;
Name Null? Type
--------------------------------------- 

RES_ID NOT NULL NUMBER
TIME DATE
HOTEL_ID NUMBER

SQL> alter table res add (test number);
alter table res add (test number)*ERROR at line 1:ORA-12081: update operation not allowed on table "SURACHART"."RES"

SQL> alter table res modify shrink space;
alter table res modify shrink space*ERROR at line 1:ORA-12081: update operation not allowed on table "SURACHART"."RES"

SQL> drop table res;
Table dropped.

SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$RrzjPv1zBxXgQFs98uYGtg==$0 TABLE

SQL> desc res;
ERROR:ORA-04043: object res does not exist

SQL> flashback table res to before drop;
Flashback complete.

SQL> desc res;
Name Null? Type
-----------------------------------------------------

RES_ID NOT NULL NUMBER
TIME DATE
HOTEL_ID NUMBER

SQL> delete from res;
delete from res *ERROR at line 1:ORA-12081: update operation not allowed on table "SURACHART"."RES"

SQL> alter table res read write ;
Table altered.

SQL> delete from res;
1 row deleted.


------------------------------------------------------------------