Add New Skills!

Udemy
Showing posts with label Oracle 11g Read Only tables. Show all posts
Showing posts with label Oracle 11g Read Only tables. Show all posts

Tuesday, November 29, 2011

Oracle 11g Read Only tables

You can now create read only tables in Oracle 11g. This new feature can come in handy when it is desirable to store monthly or yearly snapshots protected from modifications.

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 29 06:29:59 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn hr/..@xe
Connected.
SQL> CREATE TABLE REGIONS_RO AS SELECT * FROM REGIONS;

Table created.

SQL> SELECT * FROM REGIONS_RO;

REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> ALTER TABLE REGIONS_RO READ ONLY;

Table altered.

SQL> SELECT * FROM REGIONS_RO;

REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

-- INSERT NOT ALLOWED ON READ ONLY TABLES

SQL> INSERT INTO REGIONS_RO(REGION_ID, REGION_NAME) VALUES(5, 'Unknown');
INSERT INTO REGIONS_RO(REGION_ID, REGION_NAME) VALUES(5, 'Unknown')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."REGIONS_RO"

-- UPDATE NOT ALLOWED ON READ ONLY TABLES

SQL> UPDATE REGIONS_RO SET REGION_NAME='EU' WHERE REGION_ID=1;
UPDATE REGIONS_RO SET REGION_NAME='EU' WHERE REGION_ID=1
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."REGIONS_RO"

--DDLS ALLOWED
SQL> CREATE INDEX IDX_REGION_ID ON REGIONS_RO(REGION_ID);

Index created.

-- SET TO READ WRITE TO ENABLE WRITE

SQL> ALTER TABLE REGIONS_RO READ WRITE
  2  ;

Table altered.

SQL> INSERT INTO REGIONS_RO(REGION_ID, REGION_NAME) VALUES(5, 'Unknown');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE REGIONS_RO READ ONLY;

Table altered.

SQL> INSERT INTO REGIONS_RO(REGION_ID, REGION_NAME) VALUES(5, 'Unknown');
INSERT INTO REGIONS_RO(REGION_ID, REGION_NAME) VALUES(5, 'Unknown')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."REGIONS_RO"