Re: Make table read-only

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 21 Oct 2004 16:55:38 +1000
Message-ID: <41775d62$0$4310$afc38c87@news.optusnet.com.au>

Howard J. Rogers wrote:

> MS wrote:
>

>> In addition to the trigger to disable DML, disabling table lock does >> the trick.

Ah. I'll answer my own question then. I see I am to disable the table lock *in addition* to writing the triggers etc. which are actually to do the ordinary DML prevention. My apologies for misunderstanding. I had hoped this one command would do the lot.

I'm less excited about it now, I'm afraid, than I was!

Regards
HJR

>> 
>> So, for the mentioned example:
>> 
>> SQL> alter table t disable table lock;
>> 
>> This prevents sqlldr direct load, as well as truncate, and DDL's on
>> this table (including drop table).
>> 
>> 
>> -Madhu S

>
> Madhu, have you actually tried this?
>
> For example, in my 9i Release 2 database on Windows, I get this:
>
> SQL> select sum(sal) from scott.emp;
>
> SUM(SAL)
> ----------
> 29025
>
> SQL> alter table scott.emp disable table lock;
>
> Table altered.
>
> SQL> update scott.emp set sal=9;
>
> 14 rows updated.
>
> SQL> select sum(sal) from scott.emp;
>
> SUM(SAL)
> ----------
> 126
>
> Which doesn't look very read-only at all! That the 'disable lock' command
> has done something is certain, because if as Scott I try:
>
> SQL> lock table emp in exclusive mode;
> lock table emp in exclusive mode
> *
> ERROR at line 1:
> ORA-00069: cannot acquire lock -- table locks disabled for EMP
>
> But the records were definitely updated, and therefore, nice idea though
> it was, I can't see that it does what you claimed it would do. Any
> clarification, please? Am I doing something wrong, or what?
>
> Regards
> HJR
Received on Thu Oct 21 2004 - 01:55:38 CDT