How to Make a Table Read-only in Oracle

Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command:

To make it writable again, issue the following command:

In previous versions of Oracle, there was no command to make a table read-only, but we can use a little trick to do it. All we need is to create a constraint and make it disable:

Because the constraint is disabled, it is not enforced but it will not let you change the data to ensure that data still conforms to the constraint’s requirements. Unfortunately, direct path loads will bypass this, so we also need to issue the following command:

This will also prevent user to drop this table. Another option is to mark the tablespace which contains the table as read-only, but it’s not practical because it wll make all the objects read-only residing in the tablespace.

Please share
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

2 Comments

  1. Mini

    Hi Gokhan,

    Could you explain what happens when you execute the following query?:

    ALTER TABLE our_table READ ONLY;

    I tried to make a couple of my tables as READ ONLY but I am still able to update the information of it.

    Is there something else that I am missing?

    • Gokhan Atil

      Well, I don’t know what you’re missing but I tested it again, and confirmed that it doesn’t get modified.

Leave Comment

Your email address will not be published. Required fields are marked *