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.

Oracle Table Functions

A table function is a PL/SQL function which will behave like a row source when queried. So you can perform transformations to the data before it is returned in the result set.

To write a table function, first we need to define an object as the row structure:

Because our function will return a table, we’ll also define a type: