Saturday, November 13, 2010

Oracle: "Read-Only" Table?

Let's say you want to make an entire tablespace within an Oracle instance READ ONLY; No biggie, Oracles got you covered. Simply execute the following:

ALTER TABLESPACE READ ONLY;

However, change that desire to just setting a table to READ ONLY and Oracle does not have a simple little concise statement for that. Yet what you want to accomplish can still be done fairly easily. For example:

ALTER TABLE ADD CONSTRAINT check(1=1) disable validate;

**Don't forget that there is a max number of charters (30) allowed in the constraint name.

Now if anyone tries to insert, update or delete from that table they will raise an ORA-25128 error.

Here an example for you to look at:

alter table PLANUSER2.UAP_TT_TOOL_TAB add constraint PLANUSER2_UAP_TT_TOOL_TAB_RO check(1=1) disable validate;


SQL> delete from PLANUSER2.UAP_TT_TOOL_TAB;
delete from PLANUSER2.UAP_TT_TOOL_TAB
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint
(PLANUSER2.PLANUSER2_UAP_TT_TOOL_TAB_RO) disabled and validated

Thx

Updated "Help Understanding ORA-600 ERRORS"

There is now a more recently updated (last modified October 2010) version of the information I posted on Oracle's support site (metalink.oracle.com). Search the knowledge base for Note: ORA-600 Lookup Error Categories [ID 175982.1]
Thx