Re: Make table read-only

Fabrizio wrote:

> This is a trick I read while studying for OCP:
>
> to make a table "read only" set a constraint to "DISABLE VALIDATE".
> No delete, update or insert will be permitted on that table.
> If there is no constraint you can create a "dummy" one.
>
> Never tested but it should work.
>
> Regards
> Fabrizio

It's not a trick. The disabled novalidate state of a constraint is there to lock a segment from having DML performed against it whilst index or other maintenance is being performed.

You can't drop an index if it's used to enforce a constraint. To drop it, you disable the constraint -but that would allow users to pile all sorts of violating records into the table. So in 8i and above, you can disable but VALIDATE, meaning the constraint's switched off (so the index can be dropped) but the table is locked, so no violating records can be introduced into it whils your index maintenance activity takes place.

The essence of that scenario is that the constraint is TEMPORARILY disabled, but validated, only for as long as it takes to perform your maintenance operations. The intention on Oracle's part at least was, and is, that the constraint should be fully enabled again once those operations are finished.

Your suggestion is therefore certainly clever, but it again is a dangerous one, in the sense that it doesn't *really* make the table read only (because I can always alter table X enable constraint Y). It would be a risky proposition to think 'my data is safe' using this technique, because it's safe only so long as no-one re-enables the constraint.

It is also risky, because altering the status of constraints can affect the optimiser, so execution plans could go awry.

And it is a management nightmare, too, because you would not ordinarily want a constraint to be left in the disabled state permanently -but presumably this is now an exception... and exceptions are always a pain to administer.

I wouldn't be recommending this approach for long-term (more than a few hours) in a production environment as a way of protecting data against DML activity.

Regards
HJR Received on Tue Oct 19 2004 - 14:31:14 CDT