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.
1 comment:
Didn't know about this feature, so thanks. It's an easy way to make a table read-only.
However, making a table read-only is possible ever since triggers are around:
Create an "on insert or update or delete" trigger, either at row or statement level and make it do:
begin
raise_application_error (-20123,'Changing data in this this table is not allowed');
end;
/
Of course you could make it a bit more fancy bij giving different messages for different types of DML. In which case the only real difference with the 11g option is the number of the ora-error
Post a Comment