Oracle SQL: Read-Only Tables

1. Overview & Core Concepts

• Definition: Oracle allows you to restrict tables to read-only mode, blocking all Data Modification Language (DML) operations (such as INSERT, UPDATE, DELETE) and certain Data Definition Language (DDL) modifications while permitting SELECT queries.
• Primary Use Cases: Protecting reference data, historical archives, compliance data retention, and locking master tables during maintenance or migration windows.

2. Methods to Make a Table Read-Only or Restrict Data Modifications

There are multiple approaches in Oracle to enforce read-only behavior on table data, ranging from native table states to permissions, views, and triggers.

Method 1: Using Native Table Read-Only State (ALTER TABLE ... READ ONLY)

This is the cleanest, built-in feature introduced in Oracle to directly set a table's state to read-only.

• Syntax:SQL
  

  ALTER TABLE emp READ ONLY;
  

• Reverting to Read-Write:SQL
  

  ALTER TABLE emp READ WRITE;
  

• Behavior & Error Handling:

  • If you attempt to insert or update records on a read-only table, Oracle throws a direct error:
    • SQL Error: ORA-12081: update operation not allowed on table "EMP"

Method 2: Granting Restricted Privileges Across Schemas

You can isolate the master table in a dedicated schema (Schema1 / User-A) and grant only SELECT privileges to other users (Schema2 / User-B).

• Setup & Granting Privilege:SQL
  

  -- Connected as Schema1 (User-A)
  GRANT SELECT ON emp TO user_b;
  

• Behavior from User-B's Schema:SQL
  

  -- Connected as Schema2 (User-B)
  INSERT INTO user_a.emp VALUES (..., ...);
  

  • Error Output: ORA-01031: insufficient privileges (or general insufficient privileges error).

Method 3 & 4: Creating Views (Standard or Read-Only Views)

• You can revoke direct DML access to the base table and expose data strictly via a view.

• Starting in Oracle, you can explicitly create a read-only view to block DML directly through the view
  

  CREATE OR REPLACE VIEW emp_ro_view AS
  SELECT * FROM emp
  WITH READ ONLY;
  

Method 5: Using Statement-Level Triggers

You can implement a database trigger to intercept and block any DML transactions at the statement level.

• Syntax & Example:SQL
  

  CREATE OR REPLACE TRIGGER tr_block_dml
  BEFORE INSERT OR UPDATE OR DELETE ON emp
  BEGIN
      RAISE_APPLICATION_ERROR(-20001, 'Table is read-only. DML operations are not allowed.');
  END;
  /
  

3. Summary Comparison of Read-Only Approaches

Method	Pros	Cons / Limitations
ALTER TABLE READ ONLY	Native, high-performance, dictionary-enforced, prevents accidental DML by table owners/admins without switching mode.	Requires exclusive table access lock to change state.
Schema Privileges	Clean separation of duties; non-owners cannot write.	Owner of the schema can still perform DML.
Read-Only Views (WITH READ ONLY)	Granular column/row filtering while keeping base table secured.	Users with direct table access can still modify the base table.
Triggers	Highly customizable; can include conditional logic (e.g., allow specific users).	Overhead on every DML statement; can be disabled by users with ALTER TRIGGER privileges.

4. Advanced Interview Insights & Frequently Asked Questions

NOTE: Interviewers frequently probe into the metadata behavior and edge cases of read-only tables.

• Can you drop or truncate a read-only table?
  • No. DDL operations that modify the table structure or contents (like DROP TABLE or TRUNCATE TABLE) are blocked when a table is in READ ONLY mode. You must first alter the table back to READ WRITE (ALTER TABLE tab_name READ WRITE;) before dropping or truncating.
• Can indexes be created or rebuilt on a read-only table?
  • Dropping or creating indexes is generally restricted while the table is read-only because it modifies the table's segment metadata.
• How can you verify if a table is currently in read-only mode?
  • You can query the READ_ONLY column from data dictionary views like USER_TABLES, ALL_TABLES, or DBA_TABLES

    ```
    SELECT table_name, read_only
    FROM user_tables
    WHERE table_name = 'EMP';
    ```