Tuesday, April 9, 2013

Read only tables in Oracle 11g

Oracle 11g database categorizes tables based on their transactional behavior; they can be READ ONLY or READ WRITE. A READ ONLY table remains passive against all DML operations, selective DDL operations, and flashback activities. The permissible actions on a READ ONLY table includes selection, indexing, enforce constraints, rename, and dropping.

With the addition of this category, Oracle added another obvious category as READ WRITE. A table, which is open for all transactional activities, falls under this category. The category can be toggled over at any point of time in the session using ALTER TABLE command.

A table would be created in conventional manner but it can be altered to READ ONLY mode.

Example

ALTER TABLE [TABLE NAME] [READ ONLY | READ WRITE]

Example

SQL> ALTER TABLE EMPLOYEES READ ONLY;
Table altered.

The below ALTER TABLE statement switches back the table mode to READ WRITE.


SQL> ALTER TABLE EMPLOYEES READ WRITE;
Table altered.
READ ONLY tables are extremely useful in tightening the security at user level. Earlier, the same objective was achieved by a statement level DML trigger or a check constraint in ‘disable validate’ state. But READ ONLY table provides a simple and reliable technique to impose DML restriction on a table.

No comments:

Post a Comment