Before 11g, if you want table to be read-only, you have to do some work-around like,
- By Granting only Select to Users
- By making an entire tablespace read-only, which meant you had to group all the tables that you wanted to be read-only into a common tablespace or set of tablespaces.
- Or by creating a trigger that would cause an error, preventing data from being inserted.
Oracle Database 11g now allows you to make a single table read-only without affecting the other tables in the tablespace, which helps save the overhead of having to move tables to read-only tablespaces when you are ready to make the table read-only. Not only can you modify the table to be read-only, but also you can change it back to read-write to allow inserts and updates when needed.
On a Read Only table, all types of DML & DDL operations will stop except Some DDL operations can still be performed, such as Drop Table & Alter Table add / coalesce / merge / modify / move / rename / split and Partition.
If you want to verify, if a table is Read Only or not, Oracle has added a new column in tables like DBA_TABLES, ALL_TABLES and USER_TABLES called READ_ONLY which is set to Yes or No.
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;
As of now, you can’t make a table partition Read Only. To do so, you have to move the partition to a tablespace that can be marked Read Only.