Prior to Oracle Database 11g, you could make 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 you had to create 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.
The read-only designation for a table will stop all DML (truncate/insert/update/delete/etc.) operations and certain DDL operations as well, such as ALTER TABLE to add/modify/rename/drop columns, ALTER TABLE drop/truncate/exchange (SUB)PARTITION, ALTER TABLE upgrade (see the Oracle docs for all restrictions). Some DDL operations can still be performed on a table designated read-only, such as DROP TABLE, ALTER TABLE add/coalesce/merge/modify/move/rename/split (SUB)PARTITION, ALTER TABLE rename/move/add supplemental log/drop supplemental log/deallocate unused.
To identify if a table is read-only, a new column called READ_ONLY has been added to DBA_TABLES, USER_TABLES, and ALL_TABLES. This column is set to YES if the table is read-only and NO when the table is not read-only. Unfortunately, at this time, Oracle does not allow you just to take a table partition and make it read-only. To make a table partition read-only, the partition must be moved to a tablespace that can be marked read-only.
To put a table into read-only:
To take a table out of read-only:
Leave a Reply