Make a table read only
                                                 Last update (2008-02-24 21:26:30)
                                                                                                                   Date added (2008-02-20 15:30:41)

Summary
You cannot make a single table read only. You can make read only a tablespace but this will make read only all the tables in it. So one workaround is to move to table you want to an new tablespace and then make read only this tablespace.
alter table TEST move tablespace NEW_TABLESPACE nologging;
with this action all the indexes of the table will became invalid, so rebuild them. To get the sql of rebuilding indexes use
SELECT 'alter index ' || owner || '.' || index_name || ' rebuild nologging;' 
FROM dba_indexes 
WHERE table_name LIKE 'TEST'
--AND owner = 'GL';
After that you can make the NEW_TABLESPACE read only.
ALTER TABLESPACE  READ ONLY;
To make the tablespace back to read write mode, issue the command:
ALTER TABLESPACE  READ WRITE;
One great workaround to make the table read only is to create a trigger.
CREATE OR REPLACE TRIGGER test_read_only 
BEFORE INSERT OR UPDATE OR DELETE ON TEST
BEGIN
RAISE_APPLICATION_ERROR (-20001, 'Table TEST is read only, you cannot modify data.');
END;
/
When you'll try to delete, you will get:
ERROR AT line 1:
ORA-20001: TABLE TEMP IS READ ONLY, you cannot MODIFY DATA.
ORA-06512: AT "TEMP_READ_ONLY", line 2
ORA-04088: error during execution OF TRIGGER 'TEMP_READ_ONLY'
Tip: You can still TRUNCATE the table!!!!

Another workaround is simply create a check constraint on the table while specifying disable validate.
ALTER TABLE test ADD CONSTRAINT test_read_only check(1=1) disable validate;
Trying to insert, delete or update that table would yield the an ORA-25128: No insert/update/delete on table with constraint disabled and validated error and prevent the DML operations.
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search