8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Read-Only Tables in Oracle Database 11g Release 1

In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.

alter table table_name read only;
alter table table_name read write;

The following script creates a table, inserts a row, then sets the table to read-only.

drop table ro_tab purge;

create table ro_tab (
  id  number
);

insert into ro_tab values (1);
alter table ro_tab read only;

Any DML statements that affect the table data and SELECT ... FOR UPDATE queries result in an ORA-12081 error message.

SQL> insert into ro_tab values (2);
insert into ro_tab values (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


SQL> update ro_tab set id = 2;
update ro_tab set id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


SQL> delete from ro_tab;
delete from ro_tab
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


declare
  l_id ro_tab.id%type;
begin
  select id
  into   l_id
  from   ro_tab
  where  rownum = 1
  for update;

  rollback;
end;
/
declare
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"
ORA-06512: at line 4

SQL>

DDL statements that affect the table data are also restricted.

SQL> truncate table ro_tab;
truncate table ro_tab
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


SQL> alter table ro_tab add (description varchar2(50));
alter table ro_tab add (description varchar2(50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"

Operations on indexes associated with the table are unaffected by the read-only state. DML and DDL operations return to normal once the table is switched back to read-write mode.

SQL> alter table ro_tab read write;

Table altered.

SQL> delete from ro_tab;

1 row deleted.

SQL>

The read-only status of tables is displayed in the READ_ONLY column of the [DBA|ALL|USER]_TABLES views.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.