jump to navigation

Read-Only Table Before 11g (A Day In The Life) May 15, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Opinion, Read Only.

An excellent question by fellow Aussie Chris Muir on this OTN Thread reminded me of a little trick I picked up in my travels.

Basically the question is how can one make a table read-only before 11g ?

The thread mentions a number of possibilities, some better than others. I thought I might just mention this possible solution involving Materialized Views. There are various alternatives based on this basic idea, this is just a simple example.

First create and populate a table we want to convert to Read-Only.

SQL> create table bowie_ro (id number, name varchar2(20));

Table created.

SQL> alter table bowie_ro add primary key (id);

Table altered.

SQL> insert into bowie_ro values (1, ‘Bowie’);

1 row created.

SQL> insert into bowie_ro values (2, ‘Ziggy’);

1 row created.

SQL> insert into bowie_ro values (3, ‘Major Tom’);

1 row created.

SQL> commit;

Commit complete.

Next, rename the table to a another name.

SQL> rename bowie_ro to bowie_temp;

Table renamed.

Next, create a materialized view called the original name based on the renamed table.

SQL> create materialized view bowie_ro
2 refresh on demand complete
3 as select * from bowie_temp;

Materialized view created.

Next, drop the orignal table.

SQL> drop table bowie_temp;

Table dropped.

We can now see and select the table as we could previously.

SQL> select * from bowie_ro;

ID         NAME
1          Bowie
2          Ziggy
3          Major Tom

However, you now can’t perform DML on the table, making it effectively read-only …

SQL> insert into bowie_ro values (4, ‘Thin White Duke’);
insert into bowie_ro values (4, ‘Thin White Duke’)
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

You may of course need to add a few grants, constraints or indexes here or there but the table is now effectively read-only without the need of a read-only tablespace or other trick as mentioned in the OTN thread.

Back to indexes accessing all rows in a table soon 🙂