Skip to content

Alter Table Read Only Examples

  • by

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
  • Any kind of DML is prohibited to prevent data from any change.

  • TRUNCATE
  • Although TRUNCATE is a DDL, not DML, destroying all data is not acceptable in READ ONLY mode.

  • SELECT FOR UPDATE
  • Although SFU only locks returned rows, it's still unacceptable.

  • Rename columns

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
  • 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.

  • Expand column size
  • Create indexes for this table
  • Rename the table
  • Move the table
  • Moving a table may change the physical storage attribute, but the data remains unchanged.

  • Drop the table
  • Yes, you can drop a READ ONLY table. Does it seem weird?

  • Flashback the table
  • A dropped READ ONLY table can be flashed back from recycle bin, moreover, it's still READ ONLY.

  • Lock the table
  • Explicit LOCK TABLE is OK, but it may not be as useful as you think.

  • Rename constraints

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.

Leave a Reply

Your email address will not be published. Required fields are marked *