Tuesday, June 3, 2008

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 11GIf 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 partitionsTRUNCATE TABLESELECT FOR UPDATEALTER TABLE ADD/MODIFY/RENAME/DROP COLUMNALTER TABLE SET COLUMN UNUSEDALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITIONALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependentsOnline redefinitionFLASHBACK TABLE
The following operations are permitted on a read-only table:SELECTCREATE/ALTER/DROP INDEXALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINTALTER TABLE for physical property changesALTER TABLE DROP UNUSED COLUMNSALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITIONALTER TABLE MOVEALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINKRENAME TABLE and ALTER TABLE RENAME TODROP TABLEALTER TABLE DEALLOCATE UNUSEDALTER TABLE ADD/DROP SUPPLEMENTAL LOG

No comments: