//
you're reading...
Administration

Read-Only Table

Prior to Oracle Database 11g, there was a facility to place an entire tablespace in Read-Only mode but none for single table. Either you place the table in that tablespace or write trigger on table that would raises an exception to prevent data being modify in the events of DML operations.

Oracle Database 11g allows you to place a single table in Read-Only mode. That means user can query the table but no DML operations are allowed. You even change Read-Only to Read-Write mode anytime you want to allow inserts, update etc. operations.

hr@O11R2>CREATE TABLE tab_read_only
   (id NUMBER,
   name VARCHAR2(25),
   salary number);

Table created.

hr@O11R2>INSERT INTO tab_read_only VALUES(100, 'Shanta King', 30000);
1 row created.

hr@O11R2>INSERT INTO tab_read_only VALUES(100, 'Sunder Nath', 23000);
1 row created.

To place a table in Read-Only mode:

ALTER TABLE tab_read_only READ ONLY;

To place a table in Read/Write mode:

ALTER TABLE tab_read_only READ WRITE;

The Read-Only mode of the table prevents all DML operations as well as any SELECT … FOR UPDATE statements and some DDL operations as long as they modify the table data. For example, you can perform DROP operation in Read-Only mode but cannot perform TRUNCATE operation.

hr@O11R2>ALTER TABLE tab_read_only READ ONLY;
Table altered.

hr@O11R2>UPDATE tab_read_only
     SET salary=salary+1000;

UPDATE tab_read_only
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TAB_READ_ONLY"


hr@O11R2>DELETE tab_read_only;

DELETE tab_read_only
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TAB_READ_ONLY"

hr@O11R2>ALTER TABLE tab_read_only ADD commission NUMBER(3,2);

ALTER TABLE tab_read_only ADD commission NUMBER(3,2)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TAB_READ_ONLY"


hr@O11R2>TRUNCATE TABLE tab_read_only;

TRUNCATE TABLE tab_read_only
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TAB_READ_ONLY"

hr@O11R2>DROP TABLE tab_read_only;
Table dropped.

To check if a table is marked as Read-Only, query the READ_ONLY column of the *_tables views. If the column has YES value in it then the table is Read-Only.

hr@O11R2>SELECT table_name, read_only FROM all_tables;

TABLE_NAME                     REA
------------------------------ ---
. . . . . 
EMPLOYEES                      NO
JOB_HISTORY                    NO
PLAN_TABLE                     NO
V_COL_TAB                      NO
TAB_READ_ONLY                  YES
. . . . .

109 rows selected.

You can perform operations on index/indexes associated to the table even the table is in Read-Only mode.

hr@O11R2>CREATE INDEX ind_read_only ON tab_read_only(name);
Index created.

hr@O11R2>ALTER TABLE tab_read_only READ ONLY;
Table altered.

hr@O11R2>ALTER INDEX ind_read_only UNUSABLE;
Index altered.

hr@O11R2>ALTER INDEX ind_read_only REBUILD;
Index altered.

hr@O11R2>DROP INDEX ind_read_only;
Index dropped.

Hope this will help you. Happy Learning… 🙂

LINK:
ALTER TABLE – READ ONLY MODE

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: