While visiting a customer, we had a conversation about the correct way to stop users from writing to code tables. The customer described his ancient logic: when he wanted to move table to a read only state, he removed the write (insert/update/delete) permissions from all of his users. That was good enough for him for years – since he started using this method way back in Oracle 8. All was well, util this week he found out that the table owner’s privileges could not be revoked – and due to a bug, someone accidentally deleted the table content…

So I asked him why didn’t they change it to read only tables and he had no idea what I am talking about.
Well, I wrote about read only table like 6 years ago (in Hebrew), but I never thought I should translate it – since it’s a fairly “old” (11gR1) feature and everybody knows about it, right?
Well, this week proved me wrong. Here is what I wrote about it years ago.

Starting Oracle 11gR1 (which was like 3000 years ago), we can set a table to be a read only state. That means that while in read only state, we cannot change the structure or data, but we can continue read the data without any problem. The setting is not reliant on the object permissions so it means that even the table owner could not change the data/structure.

So, let’s see an example first:

SQL> drop table zohar1;
Table dropped.

SQL> CREATE TABLE zohar1 (a NUMBER, b VARCHAR2 (20), my_date DATE);
Table created.

SQL> alter table zohar1 read only;
Table altered.

SQL> INSERT INTO zohar1 (a, b, my_date) VALUES (10, 'aaa', SYSDATE);
INSERT INTO zohar1 (a, b, my_date) VALUES (10, 'aaa', SYSDATE)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "ZELKAYAM2"."ZOHAR1"

SQL> delete zohar1
  2  /
delete zohar1
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "ZELKAYAM2"."ZOHAR1"

SQL> alter table zohar1 read write;
Table altered.

SQL>  INSERT INTO zohar1 (a, b, my_date) VALUES (10, 'aaa', SYSDATE);
1 row created.

SQL> commit;
Commit complete.

So, while the table is in read only state, the writing are blocked and we cannot do the following:

• insert, update, delete, merge commands
• truncate command
• some alter commands (adding columns for example)

The read only setting does not include dropping or moving the table so watch out for that.

How do we know if the table is in read only state? we can user the *_TABLES view – and look for the READ_ONLY column:

SQL> select table_name, READ_ONLY from user_tables
where table_name = 'ZOHAR1'
/
  2    3
TABLE_NAME                     REA
------------------------------ ---
ZOHAR1                         YES

I hope it helps anyone – but I feel like the late comer to that party… 🙂