Read Only Table

Ready only table, what the heck is ready only table? Well, it is self explanatory by it's name. This table type allows user to SELECT but nothing else. Before we discuss more on READ ONLY TABLE. let's talk about the background and history of this type of table. Prior to Oracle 11g, the only way to make table read only is to grant SELECT object privilege to a user. How about the Schema Owner? Schema owner can do anything to their tables and objects. Well, this is a partial solution to a problem we are trying to tackle but not all. The owner can do damage by running wrong DDL or DML, correct? Have you not heard of those horror stories where table are truncated, dropped or updated illegally?

Adding table into Read-Only tablespace is another solution prior to version 11g. This is a great solution but there is a pain involved in moving table from one tablespace to read-only tablespace. 11g and later version of Oracle now offers a feature to mark read only table in any tablespace where the schema owner itself cannot run any DDL and DML except for SELECT. How cool is that? Isn't this what you are looking for? pain-free process!

SYNTAX: ALTER TABLE ..READ ONLY statement marks table into READ ONLY mode while ALTER TABLE ... READ WRITE will reverse READ ONLY behavior.

ALTER TABLE employee READ ONLY;
This example marks employee table to READ ONLY mode:

ALTER TABLE employee READ WRITE;
This syntax returns READ ONLY to READ and WRITE mode:

Privilege: To place a table in a Read-Only mode, you must have the ALTER TABLE privilege on the table or the ALTER ANY TABLE privilege. In addition, the COMPATIBILE initialization parameter must be set to 11.1.0 or greater.

Purpose: An example of a table for which read-only mode makes sense is a configuration, lookup and any table which data does not get changed. If your application contains configuration tables that are not modified after installation and that must not be modified by users, your application installation scripts can place these tables in Read-Only mode. This protect the user from unintentional changes that are not reversible and saves anyone from trouble.

Allowed: The following operations are permitted on a read-only table:

1. SELECT
2. CREATE/ALTER/DROP INDEX
3. ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
4. ALTER TABLE for physical property changes
5. ALTER TABLE DROP UNUSED COLUMNS
6. ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITION
7. ALTER TABLE MOVE
8. ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK
9. RENAME TABLE and ALTER TABLE RENAME TO
10. DROP TABLE
11. ALTER TABLE DEALLOCATE UNUSED
12. ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

Restriction: The following DDL AND DML operations are not allowed and return error:

1. TRUNCATE TABLE
2. SELECT FOR UPDATE
3. ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
4. ALTER TABLE SET COLUMN UNUSED
5. ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
6. ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents
7. ONLINE REDEFINATION
8. FLASHBACK TABLE

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. Oracle partitioned table can't be altered to read only at this time, however you can move it to read-only tablespace. Now, identify any tables that should be read-only and you know what to do next.