Sunday, August 02, 2009
Oracle read only tables
Some time ago I was having a discussion with someone on making a table in Oracle read only. I was under the impression this was possible and he was under the impression it was not possible. While having the conversation we both used google to find something to show to the other. He came with a page stating that it indeed was not possible and you should arrange this by revoking write access to the table for a certain user. I however came up with a page stating that it was possible. As the proof is in the pudding we tried it out and yes you can make a table read only.
The function to make a table read only is available since Oracle 11G. If you look in the SYS.USER_TABLES table you will find a column named READ_ONLY. So if you query this for a "normal table" lets say table TESTTABLE the value of SYS.USER_TABLE.READ_ONLY will read "NO". Now we set the TESTTABEL to read only by executing the following SQL command:
alter table TESTTABLE read only;
query SYS.USER_TABLE.READ_ONLY again for all records having TABLE_NAME set to TESTTABLE and you will see that it is set to "YES". If you now want to do some insert for example on the table you will get a ORA-12081: update operation not allowed on table.
So yes, it is possible to make a table read only in Oracle database 11g. Also a good read on read only tables can be found on this website.