Re: Make table read-only

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 19 Oct 2004 06:10:14 +1000
Message-Id: <41742322$0$10347$afc38c87@news.optusnet.com.au>

Steve Howard wrote:

> Ralf Zwanziger <ralf_on_duty_at_gmx.de> wrote in message
> news:<7n57n0t979rtkrvpcploqj7u37fqk9k6gu_at_4ax.com>...

>> Is ist possible to make an oracle table read-only?
>> (without moving it to a read-only tablespace).
>> I haven't found any command like "alter table xxx read only" in the
>> docs.
>> 
>> Bye,
>> Ralf

>
> In addition to the other posts, I will just add that you could,
> although not as desirable, add a trigger which would prevent any
> changes.

No... the word "any" is incorrect there, as I posted earlier today... but here's proof. I'll follow your (excellent) example, mostly.

SQL> connect scott/tiger
SQL> create table t(c number);

Table created.  

SQL> declare

   2 i number;
   3 begin
   4 for i in 1..100 loop
   5 insert into t values(i);
   6 end loop;
   7 end;
   8 /  

PL/SQL procedure successfully completed.  

SQL> commit;  

Commit complete.  

SQL> create or replace trigger t_trg
  2 before delete or update or insert on t   3 for each row
  4 begin
  5 RAISE_APPLICATION_ERROR(-20001,'No DML!');   6 end;
  7 /

Trigger created.  

SQL> update t set c = 12;
update t set c = 12

       *
ERROR at line 1:

ORA-20999: No DML!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'

SQL> insert /*+ append */ into t nologging select * from t; insert /*+ append */ into t nologging select * from t;

                          *

ERROR at line 1:

ORA-20999: No DML!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'

[Which, incidentally, goes to prove that I was wrong earlier to state that insert triggers don't fire during direct load inserts. They do, clearly].

SQL> select count(*) from t;

  COUNT(*)

---

       100

[New terminal window]

[oracle_at_opal oracle]$ sqlldr scott/tiger control=t.ctl direct=true

SQL*Loader: Release 10.1.0.2.0 - Production on Tue Oct 19 06:00:58 2004

Load completed - logical record count 8. [oracle_at_opal oracle]$ sqlplus scott/tiger

SQL> select count(*) from t;

  COUNT(*)

---

       108

(The t.ctl simply asked to load t.csv into table t. T.csv in its turn was just a list of numbers 1 to 8).

Direct SQL Loads really *don't* fire insert triggers, and hence the table isn't truly read only.

My only point here is not to rubbish the idea of before DML triggers, because they clearly rule out most DML possibilities. But I'd hate someone to rely on it as a means of, say, "guaranteeing" the data is free from all changes from this point on, because it's not. I doubt it would pass 'archiving' muster with the tax office, for example.

Regards
HJR Received on Mon Oct 18 2004 - 15:10:14 CDT