Hi gang

I'm just writing up a database quiz for a local user group and I was hoping I could get a bit of inspiration from the database experts.

One of the questions will be "prior to 11g with the introduction of read-only tables, how could you make a table read-only?". The answers I've come up with:

1) Security priviliges (schema + grant SELECT)
2) Triggers
3) Create a check constraint with disable validate
4) Read-only tablespace
5) Read-only database (standby)
6) (Slightly crazy) Create view, and instead-of triggers that do nothing (similar to 2)
7) Write the query results on a piece of paper and then turn the database off

Anybody have any other answers, real or slightly off topic like mine please?

Cheers,

CM.

You can also use DBMS_RLS package too for making the table read only

-Amit
http://askoracledba.wordpress.com/

Hi Chris

Interesting question.

I've just posted another possible option on my Blog:

http://richardfoote.wordpress.com/2008/05/15/read-only-table-before-11g-a-day-in-the-life/

Cheers

Richard Foote
http://richardfoote.wordpress.com/

Hi Don

How does that prevent the owner of the table from making modifications or from granting DML privileges to other users ?

Cheers

Richard Foote
http://richardfoote.wordpress.com/

Check constraint and trigger solutions may have problems with sqlldr direct path operations, so using it together with alter table disable lock may be mandatory depending on the needs. Especially if DDLs are also wanted to be avoided.

This topic was once mentioned on Tom Kyte's blog or asktom but I couldn't find the source to link here.

SQL> conn hr/hr
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as hr
 
-- cleaning objects
SQL> drop table tong purge ;
 
Table dropped
 
SQL> drop view vw_tong ;
 
View dropped
 
-- creating the demo table
SQL> create table tong ( col1 number ) ;
 
Table created
 
SQL> alter table tong add constraint cc_tong check ( 1=0 ) disable validate;
 
Table altered
 
SQL> alter table tong disable table lock;
 
Table altered
 
-- some DDL tests
SQL> drop table tong ;
 
drop table tong
 
ORA-00069: cannot acquire lock -- table locks disabled for TONG
 
SQL> truncate table tong ;
 
truncate table tong
 
ORA-25128: No insert/update/delete on table with constraint (HR.CC_TONG) disabled and validated
 
SQL> alter table tong parallel ;
 
alter table tong parallel
 
ORA-00069: cannot acquire lock -- table locks disabled for TONG
 
SQL> lock table tong in exclusive mode ;
 
lock table tong in exclusive mode
 
ORA-00069: cannot acquire lock -- table locks disabled for TONG
 
-- some DML tests
SQL> select * from tong ;
 
      COL1
----------
 
SQL> update tong set col1 = col1 + 1 ;
 
update tong set col1 = col1 + 1
 
ORA-25128: No insert/update/delete on table with constraint (HR.CC_TONG) disabled and validated
 
-- creating dependent objects test
SQL> create index nui_tong on tong(col1) nologging ;
 
Index created
 
SQL> create view vw_tong as select * from tong ;
 
View created
 

added comments to the code

Message was edited by:
TongucY

FGAC is a great idea. I also tried for a table. Here it is.

http://karthickarp.blogspot.com/2008/05/read-only-table.html

But in your example you have not handled INSERT i believe.

Regards,

Karthick.
http://karthickarp.blogspot.com/

> FGAC is quite complicated to work with. Just the mere fact that we don't get meaningful errors in

Er.. and just what is the point there? <confused look>

> Also it is only available in the Enterprise Edition.

Interestingly, this is about the only edition we use. So we're kind of spoiled and consider enterprise features basically as the "norm". Kinda nice...

Select the option depending on your needs and your constraints.

This is doable for a small table, certainly.

We were listing the options available -- for a quiz
not necessarily a "tightly constrained real world
scenario" but just to list all possible options.