How to Make Your Tables Read Only - 5 Ways to Achieve the Goal

To prevent owner's tables to be changed by other users is easily controlled through object privilege practices, but how to prevent the owner to change tables of his own. It's not so easy to do it, because it does not make sense and it's against the rules of a database. But sometimes, you might be demanded to do it.

There are many ways to make tables under your own schema to be allowed query-only.

1. Read only on table-level.

For 11g onwards, you can switch the tables to "read only" whenever needed, and switch back to "read write" like this.

SQL> alter table table_name read only;
SQL> alter table table_name read write;
2. Read only on tablespace-level.

You can reassign read-only table to a centralized tablespace which is defined as read-only. But this is not a usual operation. You should consider it as a temporary operation.

S QL> alter tablespace tbs read only;
3. Make validation failed to prevent DML.

If your database is not 11g or above, your can make all DML validation failed by adding a check constraint with validation disabled.

SQL> alter table table_name add constraint read_only_constraint check(1=1) disable validate;
4. Lock table in a exclusive mode.

Make the table can be accessed only by queries, no any other sessions can get locks from this table.

SQL> lock table table_name in exclusive mode;

But the drawback is the exclusive lock will disappear when the locking session is terminated due to some reasons.

5. Change the tables' owner.
SQL> create table_name new_owner.table_name as select * from old_owner.table_name;
SQL> drop table old_owner.table_name;
SQL> grant select on new_owner.table_name to old_owner;
SQL> create synonym old_owner.table_name for new_owner.table_name;

Of course, you can startup the database to read-only, but it's not practical.