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.
trackback

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 :)

About these ads

Comments»

1. H.Tonguç Yılmaz - May 15, 2008

MV solution for this was interesting to learn, thank you as usual Richard.

But why do you drop the renamed table?

2. Pete Finnigan - May 15, 2008

Hi Richard,

Interesting post. I had a number of posts on my blog over the years about read only tables. Some of which linked to no longer existing HJR posts/articles. I won’t go into the details of methods of making “read-only” tables but I do have a couple of important points to make.

The first is the need for so-called read only tables, why? – these are often created to allow developers into production or for support people (slightly better but not much) to allow analyis of issues in the database against prod data. Your example above would not support this anyway as it involves dropping the original data. I don’t recommend access in these cases.

The second issue is that if you create a so called “read-only” users – lots of details here – http://www.petefinnigan.com/weblog/archives/00000166.htm. the big issue is that even if you create a user with just CREATE SESSION and maybe select privileges (or some ideas like above) on certain production data (tables) then because of the PUBLIC issue in 10gR2 they have 21,000 other privileges, in 11gR1 27,000 other privileges. In this sense there is no such thing as a read only user or indeed read only table (because of this). disallow access is the best option.

cheers

Pete

3. Richard Foote - May 15, 2008

Hi Tonguc

No real reason other than no longer being able to update the contents of the original table either :)

4. Richard Foote - May 15, 2008

Hi Pete

I totally agree with you. The key question is why do you need to make a table read-only, what’s the actual business requirement. Does it have to be read-only permanently or only for specific periods.

Depending on the real requirement, the real or appropriate solution can be determined, which might of course be entirely different like having proper auditing implemented or some such.

The post was only meant to demonstrate how a feature could be twisted to implement something that may not be immediately obvious, it’s not something one would necessarily look at really implementing without some reasonable justification.

5. H.Tonguç Yılmaz - May 15, 2008

> The first is the need for so-called read only tables, why?

Let me share our recent case for this need and have your comments; during a data migration as an initial step in order to minimize the downtime a DBA group wanted to move the huge tables from source to target, during this period we want to be sure that these tables are really read/only. And the solution must have no performance impact and must guarantee that application users won’t be affected related to missing grants.

The problem with this case is that these “huge” tables were not partitioned on separate tablespaces so that we could keep these tablespaces as read/only happily. But this is the real world, somethings you expect to be there in first place is not there and you are expected to create another “best” solution still.

So I know some may think that there was no need for it but I am happy to see the 11g read/only table new feature.

6. karthickarp - May 16, 2008

I have created a account in wordpress. I guess i wont be a spam any more :-)

“Why we need a read only table?” This seems to be a very valid question.

Say I have a table as given below in production.

SQL> CREATE TABLE hx_my_read_only_table (no INTEGER CHECK(NO>0), name VARCHAR2(100));

Table created.

SQL> INSERT INTO hx_my_read_only_table VALUES(1,’KARTHICK’);

1 row created.

SQL> INSERT INTO hx_my_read_only_table VALUES(2,’VIMAL’);

1 row created.

SQL> INSERT INTO hx_my_read_only_table VALUES(3,’VIJAY’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM hx_my_read_only_table;

NO NAME
———- ——————————————————————————–
1 KARTHICK
2 VIMAL
3 VIJAY

And I want to make it read only. I will use ACCESS CONTROL to achieve it.

SQL> CREATE CONTEXT hx_my_context USING hx_my_pkg;

Context created.

SQL> CREATE OR REPLACE PACKAGE hx_my_pkg
2 AS
3 PROCEDURE hx_set_context (
4 hx_p_fld VARCHAR2,
5 hx_p_value VARCHAR2);
6
7 FUNCTION hx_get_val (
8 hx_p_fld varchar2) RETURN NUMBER;
9
10 FUNCTION hx_get_key(
11 p_schema in varchar2,
12 p_object in varchar2) RETURN VARCHAR2;
13 END hx_my_pkg;
14 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY hx_my_pkg
2 AS
3 PROCEDURE hx_set_context (
4 hx_p_fld VARCHAR2,
5 hx_p_value VARCHAR2)
6 IS
7 BEGIN
8 DBMS_SESSION.SET_CONTEXT(‘HX_MY_CONTEXT’, hx_p_fld, hx_p_value);
9 END;
10
11 FUNCTION hx_get_val (
12 hx_p_fld varchar2) RETURN NUMBER
13 IS
14 BEGIN
15 RETURN SYS_CONTEXT(‘HX_MY_CONTEXT’, hx_p_fld);
16 END;
17
18 FUNCTION hx_get_key(
19 p_schema in varchar2,
20 p_object in varchar2) RETURN VARCHAR2
21 IS
22 lPredicate VARCHAR2 (2000);
23 BEGIN
24 lPredicate := ‘NO = SYS_CONTEXT(”HX_MY_CONTEXT”, ”NO”)’;
25
26 RETURN lPredicate;
27 END;
28
29 END hx_my_pkg;
30 /

Package body created.

SQL> BEGIN
2 DBMS_RLS.ADD_POLICY (USER, ‘HX_MY_READ_ONLY_TABLE’, ‘HX_READ_ONLY_POLICY’,USER, ‘hx_my_pkg.hx_get_key’, ‘INSERT, UPDATE, DELETE’ );
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 hx_my_pkg.hx_set_context(‘NO’,0);
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> UPDATE hx_my_read_only_table
2 SET NAME = ‘RICHARD’
3 WHERE NO = 1;

0 rows updated.

SQL> DELETE FROM hx_my_read_only_table;

0 rows deleted.

SQL> INSERT INTO hx_my_read_only_table VALUES(4,’RICHARD’);

1 row created.

I was expecting this one not to insert record. I have to see what the problem is.

SQL> ROLLBACK;

Rollback complete.

Mean while I restricted the insert by with this trigger. I personally hate triggers but….

SQL> CREATE OR REPLACE TRIGGER hx_my_trigger BEFORE INSERT ON hx_my_read_only_table FOR EACH ROW
2 BEGIN
3 IF :NEW.NO hx_my_pkg.hx_get_val(‘NO’)
4 THEN
5 RAISE_APPLICATION_ERROR(-20001, ‘Insert Not possible…’);
6 END IF;
7 END;
8 /

Trigger created.

SQL> INSERT INTO hx_my_read_only_table VALUES(4,’RICHARD’);
INSERT INTO hx_my_read_only_table VALUES(4,’RICHARD’)
*
ERROR at line 1:
ORA-20001: Insert Not possible…
ORA-06512: at “SYSADM.HX_MY_TRIGGER”, line 4
ORA-04088: error during execution of trigger ‘SYSADM.HX_MY_TRIGGER’

So i have Restricted INSERT, UPDATE, DELETE on this table.

SQL> SELECT * FROM HX_MY_READ_ONLY_TABLE;

NO NAME
———- ——————————————————————————–
1 KARTHICK
2 VIMAL
3 VIJAY

Do comment on this method of making a table read only…

Regards,

Karthick.

http://www.karthickarp.blogspot.com/

7. Richard Foote - May 16, 2008

Hi Karthick

Unfortunately, you still get treated as spam :(

The insert fails (or rather it works) I believe as the access control predicate is not applicable within a values clause insert.

It’s another technique which could possibly be used to skin this cat although it’s certainly a little cumbersome to setup and the error message (or lack thereof) needs to be carefully considered when using this as a solution.

Personally, if you need to make a table read only, 11g is the way to go and if 11g is not possible, then I think moving the table to a read-only tablespace is the cleanest solution, although certainly not perfect and not without its problems.

8. H.Tonguç Yılmaz - May 23, 2008

On a blog dedicated to Indexes this option may not be discarded I guess :)

CREATE TABLESPACE tbs_tong_rw DATAFILE
‘D:\oraclexee\oradata\XE\tbs_tong_rw01.dbf’ SIZE 150M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/

create table tab_tong nologging parallel 2 tablespace tbs_tong_rw as
select rownum pk, ‘TONG’ dsc from dba_source ;

create unique index ui_pk on tab_tong (pk)
nologging parallel 2 tablespace tbs_tong_rw ;

CREATE TABLESPACE tbs_tong_ro DATAFILE
‘D:\oraclexee\oradata\XE\tbs_tong_ro01.dbf’ SIZE 150M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/

create index nui_dummy on tab_tong (pk, dsc)
nologging parallel 2 tablespace tbs_tong_ro ;

ALTER TABLESPACE tbs_tong_ro READ ONLY ;

– DMLs and TRUNCATE gets ORA-00372
update tab_tong set dsc = ‘UC’ where pk = 1 ;

update tab_tong set pk = 0 where pk = 1 ;

delete from tab_tong where pk = 1 ;

insert into tab_tong values ( -1, ‘UC’ ) ;

truncate table tab_tong ;

– but for other DDLs like below may be better to have
– alter table tong disable table lock;
– additionally

lock table tab_tong in exclusive mode ;

alter table tab_tong parallel ;

drop table tab_tong purge ;

For big tables indexing all columns onto a read only tablespace may not be acceptable because of storage needs, but it is still functional and index based, what do say Richard? :)

9. Richard Foote - May 26, 2008

Hi Tonguc

Creating a replica of the table as an index and creating the index in an read-only tablespace is certainly a “unique” approach :)

Clever.

However, thank goodness for read-only tables in 11g I say !!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,808 other followers

%d bloggers like this: