Re: Make table read-only

MS wrote:

> In addition to the trigger to disable DML, disabling table lock does
> the trick.
>
> 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:52:26 CDT