Read Only Table with 11G

Read Only Table with 11g ...

Sometime we need to keep table in READ ONLY mode but it is not possible before 11g but now in 11g it is possible to do just one command.

Before 11G
If we want to keep TABLE in read only mode in table

then we have to make TABLESPACE READ ONLY where table is exists or CREATE TRIGGER for this becuase there is no single command exists like below to make table READ ONLY.

SQL> alter table emp READ ONLY;

alter table emp READ ONLY

*

ERROR at line 1:

ORA-01735: invalid ALTER TABLE option

But with 11G or later

We can use below single command to make TABLE read only mode.

SQL> alter table emp READ ONLY;
Table altered.
SQL> delete emp;

delete emp

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP"

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