Read-Only Table

In Oracle 11g we can make a table read-only. It is a new feature in the Oracle 11g. Earlier we were able to make a non-system tablespace read-only.The Oracle 11g goes one step further and allows us to make a table read-only.

We have a new column called 'read_only' in dba_tables, user_tables and all_tables views which tells whether a table is read only or not.

We will not be able to execute DML statements like INSERT,UPDATE and DELETE on a read-only table. And all so we cannot add/remove columns.

Syntax:

  
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

Examples:

 
SCOTT@orcl11g>DROP TABLE test_tbl;

Table dropped.


SCOTT@orcl11g>CREATE TABLE test_tbl AS SELECT * FROM dual;

Table created.


SCOTT@orcl11g>SELECT * FROM test_tbl;

D
-
X


SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');

1 row created.


SCOTT@orcl11g>ALTER TABLE test_tbl READ ONLY;

Table altered.


SCOTT@orcl11g>SELECT table_name,read_only FROM user_tables WHERE table_name='TEST_TBL';

TABLE_NAME                     READ_ONLY
------------------------------ ----------
TEST_TBL                       YES


SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');
INSERT INTO test_tbl values ('A')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>UPDATE test_tbl SET dummy='Y' WHERE dummy='X';
UPDATE test_tbl SET dummy='Y' WHERE dummy='X'
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>DELETE FROM test_tbl;
DELETE FROM test_tbl
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl ADD (name varchar2(10));
ALTER TABLE test_tbl ADD (name varchar2(10))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl DROP COLUMN name;
ALTER TABLE test_tbl DROP COLUMN name
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"


SCOTT@orcl11g>ALTER TABLE test_tbl READ WRITE;

Table altered.


SCOTT@orcl11g>INSERT INTO test_tbl VALUES('A');

1 row created.

SCOTT@orcl11g>