ALTER TABLE READ ONLY
If you just want NO data of the table can be modified permanently or temporarily, you may change the attribute of the table into READ ONLY mode, no table lock is required.
SQL> alter table employees read only;
Table altered.
SQL> select read_only from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';
REA
---
YES
In READ ONLY mode, any data modifying like data manipulation language (DML) or SELECT FOR UPDATE has no use.
Any of the following operations is NOT allowable in READ ONLY mode.
- INSERT, UPDATE or DELETE
- TRUNCATE
- SELECT FOR UPDATE
- Rename columns
Any kind of DML is prohibited to prevent data from any change.
Although TRUNCATE is a DDL, not DML, destroying all data is not acceptable in READ ONLY mode.
Although SFU only locks returned rows, it's still unacceptable.
If you tried to do any of the above operations, error ORA-12081 prevents you from updating data in a READ ONLY table.
As long as data is intact as promised, you can still perform the following operations.
- SELECT rows
- Add columns
- Expand column size
- Create indexes for this table
- Rename the table
- Move the table
- Drop the table
- Flashback the table
- Lock the table
- Rename constraints
You can add new columns to this table in READ ONLY mode, but the operation is meaningless because you can never fill data into new columns.
Moving a table may change the physical storage attribute, but the data remains unchanged.
Yes, you can drop a READ ONLY table. Does it seem weird?
A dropped READ ONLY table can be flashed back from recycle bin, moreover, it's still READ ONLY.
Explicit LOCK TABLE is OK, but it may not be as useful as you think.
ALTER TABLE READ WRITE
READ ONLY mode of table can be returned to the previous state by altering table with READ WRITE.
SQL> alter table employees read write;
Table altered.
SQL> select read_only from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';
REA
---
NO
READ ONLY is pretty useful when you have a table containing only configuration data and you don't want anyone to change it.