Oracle 11g : Read-Only Tables New Feature & advantages

advertisements

_____________________________________________________________________________________________________________________

In Oracle 11g the read-only tables are introduced as new feature. Using this we can make a table to read-only and none of the DML operations are allowed after that. A read-only table can make read write anytime. In user_tables dictionary view one more column is added with Oracle 11g. See the demonstration below. The advantage of read-only option is we can use this during table maintenance as DML operations cannot change the table content.

Create an example table from dba_objects

SQL> create table example_tbl  as select * from dba_objects;

Table created.

SQL> desc user_tables

TABLE_NAME                                NOT NULL VARCHAR2(30)

 TABLESPACE_NAME                                    VARCHAR2(30)

………..

READ_ONLY                                          VARCHAR2(3)

SQL>  select table_name, read_only from user_tables where table_name ='EXAMPLE_TBL';

TABLE_NAME                     REA

------------------------------ ---

EXAMPLE_TBL                    NO

Convert the table to read only mode.

SQL>  alter table EXAMPLE_TBL read only;

Table altered.

SQL>  select table_name, read_only from user_tables where table_name ='EXAMPLE_TBL';

TABLE_NAME                     REA

------------------------------ ---

EXAMPLE_TBL                    YES

DML operations are not permitted on the read-only tables.

SQL> delete from EXAMPLE_TBL where rownum<2;

delete from EXAMPLE_TBL where rownum<2

            *

ERROR at line 1:

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

SQL> update example_tbl set OWNER ='SCOTT';

update example_tbl set OWNER ='SCOTT'

       *

ERROR at line 1:

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

SQL> insert into example_tbl (select * from example_tbl);

insert into example_tbl (select * from example_tbl)

            *

ERROR at line 1:

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

Apart from the above mentioned DML operations, the following operations also not allowed on the read only tables

• 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 the read only tables.

• 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

_____________________________________________________________________________________________________________________

Website Stats