How To Read-Only Tables in Oracle 11g

Read-Only Tables in Vaticinator 11g Sometime it is essential to micturate the special fare show only . Antecedent to 11g ,a translate exclusive table was achieved by using the triggers,constraints and opposite method to preclude the data from existence denatured. In umteen of those cases exclusive Gap, UPDATE, and Censor dealings were prevented spell some DDL dealing were not. In vaticinator 11g ,Tables can be scarred as register only, thence preventing the DML operation against. As performance amount of substance , read-only fare performance is quite moral because Oracle does not acquire the added elevated of maintaining internal property, there may be a lilliputian 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 * 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 * 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 Here is the Demo of the read only table. SQL> create table test (id number ,name varchar2(12)); Table created. SQL> insert into test values (1,\'joy\'); 1 row created. SQL> insert into test values (2,\'hope\'); 1 row created. SQL> insert into test values (3,\'peace\'); 1 row created. SQL> insert into test values (4,\'happy\'); 1 row created. SQL> commit ; Commit complete. SQL> select * from test ; ID NAME ---------- ------------ 1 joy 2 hope 3 peace 4 happy SQL> select table_name,status,read_only from user_tables where table_name=\'TEST\'; TABLE_NAME STATUS REA ------------------ ----------- ------- TEST VALID NO Now placing the table \"test\" in read only mode . SQL> alter table test read only; Table altered. SQL> insert into test values (5,\'sunny\'); insert into test values (5,\'sunny\') * ERROR at line 1: ORA-12081: update operation not allowed on table \"HR\".\"TEST\" SQL> delete from test; delete from test * ERROR at line 1: ORA-12081: update operation not allowed on table \"HR\".\"TEST\" SQL> truncate table test; truncate table test * ERROR at line 1: ORA-12081: update operation not allowed on table \"HR\".\"TEST\" Now bringing the table \"test\" in read write mode . SQL> alter table test read write; Table altered. SQL> insert into test values (5,\'sunny\'); 1 row created. SQL> commit ; Commit complete. SQL> select * from test; ID NAME ---------- ------------ 1 joy 2 hope 3 peace 4 happy 5 sunny