Make a table read only
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.
To get the sql of rebuilding indexes use
To make the tablespace back to read write mode, issue the command:
Another workaround is simply create a check constraint on the table while specifying disable validate.
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.
Comments
Post a Comment