There are many ways to make tables under your own schema to be allowed query-only.
- Read only on table-level. In 11g, you can switch the tables to "read only" whenever needed, and switch back to "read write" like this.
- 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.
- 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.
- 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.
- Change the tables' owner.
SQL> alter table table_name read only;
SQL> alter table table_name read write;
SQL> alter tablespace tbs read only;
SQL> alter table table_name add constraint read_only_constraint check(1=1) disable validate;
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.
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;