Re: Make table read-only

Howard J. Rogers wrote:
>
> 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
>

This solution was only a short note in a book and I never thought thoroughly about it (I'm even not sure how I could remember it). Clearly your understanding of oracle concepts is better than mine.

I only believed there was a simple way to prevent DMLs on a table even for the owner (I still have to test it) so, in my opinion, the answer to the original question should be "yes".

It happened I missed a very marginal part: the cons... :(

Kind Regards

-- 
Fabrizio Magni

fabrizio.magni_at_mycontinent.com

replace mycontinent with europe