Read Only Tables

Tables can be marked as read only in Oracle 11g, preventing users from performing DML operations against them.

Test case

Let's start by creating a test table:

SQL> grant select on sh.sales to hr;

Grant succeeded.

SQL> create table test
  2  as
  3  select * from sh.sales;

Table created.

Let see whats new in SYS.USER_TABLES:

SQL> desc user_tables
Name                   Null?    Type
---------------------- -------- ----------------
TABLE_NAME             NOT NULL VARCHAR2(30)
TABLESPACE_NAME                 VARCHAR2(30)
CLUSTER_NAME                    VARCHAR2(30)
...
READ_ONLY                       VARCHAR2(3)   <<< New in 11g

Now, we are going to check the READ_ONLY property of our TEST table:

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

TABLE_NAME  READ_ONLY
----------- ---------
TEST        NO

We can see the NO - it's mean that table is not read only,let make it read only.

SQL> alter table test read only;

Table altered.

We enabled the read only mode for our table TEST.

Let's check the status again.

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

TABLE_NAME READ_ONLY
---------- ---------
TEST       YES

Let see what happens if we exit from the session.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

As I exited from session now I'm curious whether the table in still in READ ONLY mode or not.

C:\>sqlplus hr/hr

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 27 14:03:53 2007

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

TABLE_NAME READ_ONLY
---------- ---------
TEST       YES

We can see the table is still in read only mode unlike LOCK table in exclusive mode.

SQL> select prod_id from test where rownum<3;

   PROD_ID
----------
        13
        13

In the above select, READ ONLY didn’t effect over SELECT.

SQL> update test set proid_id=11;
update test set proid_id=11
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST"

When we try to perform DML on the table, an error is produced.

Now, let's reverse the table state.

SQL> alter table test read write;

Table altered.

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

TABLE_NAME READ_ONLY
---------- --------------
TEST       NO

Now, we are checking the locking for a table level by using lock command.

C:\>sqlplus hr/hr

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 27 14:16:06 2007

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> lock table test in exclusive mode;

Table(s) Locked.

SQL> update test set prod_id=1
  2  where rownum<2;

1 row updated.

In the above scenario user hr only able to perform DML on the table test.

But if we lock it as a user SYS the any DML on the table test will be hang until I cancel the lock or I exit the SYS session.

SQL> show user
USER is "SYS"
SQL> lock table hr.test in exclusive mode;

Table(s) Locked.

Now as a user hr I try to perform DML on TEST

SQL> update test set prod_id=1
         where rownum<2;

- You can see that cursor start blinking...

Now I’m exit from SYS session let see the behavior.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

In the meanwhile the update statement executed in HR Schema.

SQL> update test set prod_id=1
    where  rownum<2;

1 row updated.

In the above test you would notice the READ ONLY option and locking the table in Exclusive mode.

Table READ ONLY option is beautiful thing added to 11g in order to perform TABLE maintenance we can make table READ ONLY to block DMLs.