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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google