Monday, June 09, 2014

Read Only Tables

Oracle 11 introduced a new ALTER TABLE syntax, which allows you to make a table READ ONLY, so I decided to try it out. First I created a table:

SQL> create table table_list
  2  as select table_name from dba_tables
  3  /
 
Table created.

SQL>

... then I made it READ ONLY:
 
SQL> alter table table_list read only
  2  /
 
Table altered.

... so when I tried to update it, Oracle gave me an error:
 
SQL> update table_list
  2  set table_name = 'BLAH'
  3  /
update table_list
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"ORACLE"."TABLE_LIST" 

SQL>

I changed the table back to READ WRITE mode like this: 
 
SQL> alter table table_list read write
  2  /
 
Table altered.

SQL>

... then I was able to update it:
 
SQL> update table_list
  2  set table_name = 'BLAH'
  3  /
 
3081 rows updated.
 
SQL>

When you create a table, it is in READ WRITE mode by default. If you try to make it READ WRITE again, you get an error. Once a table is in READ ONLY mode, you get an error if you try to make it READ ONLY again. I checked this out as follows:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL> alter table andrew read write
  2  /
alter table andrew read write
*
ERROR at line 1:
ORA-14140: table ORACLE.ANDREW is already in
read/write mode

SQL> alter table andrew read only
  2  /

Table altered.

SQL> alter table andrew read only
  2  /
alter table andrew read only
*
ERROR at line 1:
ORA-14139: table ORACLE.ANDREW is already in read-only
mode

SQL>

No comments: