Re: Make table read-only

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.

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(-20999,'You can''t make changes to the data!');
  6 end;
  7 /

Trigger created.

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

       *
ERROR at line 1:

ORA-20999: You can't make changes to the data!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'

SQL> delete from t;
delete from t

            *
ERROR at line 1:

ORA-20999: You can't make changes to the data!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'

SQL> insert into t values(101);
insert into t values(101)

            *
ERROR at line 1:

ORA-20999: You can't make changes to the data!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'

SQL> select count(*) from t;

  COUNT(*)

       100

SQL> ...just another alternative

Regards,

Steve Received on Mon Oct 18 2004 - 14:01:59 CDT