Placing a Table in Read-Only Mode in Oracle 11g

New in Oracle 11g, 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. An example of a table for which read-only mode makes sense is a configuration table. If your application contains configuration tables that are not modified after installation and that must not be modified by users, your application installation scripts can place these tables in read-only mode. The table is in read-only mode, operations that attempt to modify table data are disallowed.


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

Example with Joords table

The following example places the JOORDS table in read-only mode:

ALTER JOORDS SALES READ ONLY;

The following example returns the table JOORDS to read/write mode:

ALTER TABLE JOORDS READ WRITE;

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

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:

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 
ALTER TABLE SHRINK
RENAME TABLE 
ALTER TABLE RENAME TO
DROP TABLE
ALTER TABLE DEALLOCATE UNUSED
ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

For more information see:

• Oracle Documentation 11gR2 - Placing a Table in Read-Only Mode