Re: Make table read-only

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1098253232.730656_at_yasure...
| 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
|
| Fascinating. Where did you find this documented?
|
| And yes I expect your answer to embarass me so go ahead.
| --
| Daniel A. Morgan
| University of Washington
| damorgan_at_x.washington.edu
| (replace 'x' with 'u' to respond)

I never noticed it before, either, but it's been sitting right there in front of us for quite a while -- according to the 7.3 SQL Reference it was introduced in 7.2

v7.3 manual:

Disables DML and DDL locks on a table to improve performance in a parallel server environment. For more information, see Oracle7 Parallel Server Concepts & Administration.

A comment in the appendix under new features (which seems misplaced after ALTER TABLESPACE, but appears to apply to the ALTER TABLE example) adds:

This feature is of most use in a parallel server environment where a table lock can affect system performance.

v8, v9, and 10g manuals simply state:

Specify DISABLE TABLE LOCK to disable table locks, thereby preventing DML operations on the table.

But preface that with :

Oracle Database permits DDL operations on a table only if the table can be locked
during the operation. Such table locks are not required during DML operations.

The v7 manual comment about no DML table locks appears to be true -- started playing with it in my 8.1.7 sandbox (yes, I'll be installing 10g -- as soon as my client in Florida pays up)...

With table locks disabled, update statements don't get SX locks, just TX locks and apparently row locks. Very interesting.

++ mcs Received on Wed Oct 20 2004 - 07:14:52 CDT