How to Make a Table Read-only in Oracle
Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command:
1 |
ALTER TABLE our_table READ ONLY; |
To make it writable again, issue the following command:
1 |
ALTER TABLE our_table READ WRITE; |
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:
1 |
ALTER TABLE our_table ADD CONSTRAINT chck1 CHECK (1=1) DISABLE VALIDATE; |
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:
1 |
ALTER TABLE our_table DISABLE TABLE LOCK; |
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.