Read-Only Tables in Oracle 11g

Sometime it is necessary to make the particular table read only . Prior to 11g ,a read only table was achieved by using the triggers,constraints and other method to prevent the data from being changed. In many of those cases only INSERT, UPDATE, and DELETE operations were prevented while many DDL operations were not. In oracle 11g ,Tables can be marked as read only, hence preventing the DML operation against. As performance point of view , read-only table performance is quite good because Oracle does not have the additional overhead of maintaining internal consistency, there may be a small, but measurable reduction in resource consumption .                                                                                                                                

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

Enjoy  :-)