How to set a table in read-only mode ?
January 9th, 2006 By Francois Degrelle
ALTER TABLE emp ADD CONSTRAINT read_only CHECK (1=1) DISABLE VALIDATE;
It is now, impossible to insert, update or delete anything with this table.
13 Responses to “How to set a table in read-only mode ?”
Leave a Reply
You must be logged in to post a comment.
January 9th, 2006 at 2:24 pm
should not it be check 1=0?
January 10th, 2006 at 1:43 am
Laurent,
The content of the constraint has no importance. The trick is just behind the 2 words : DISABLE VALIDATE
January 16th, 2006 at 1:40 pm
Learned something new today!!!
January 16th, 2006 at 2:19 pm
Someone pointed out on my blog that a direct path load will bypass this, the table isn’t read only for sqlldr direct=y. Additionally, the direct=y will cause the constraint to not be effective after the direct path load.
Neat idea though…
January 16th, 2006 at 6:32 pm
The only correct answer to the question as written is “You can’t”.
If you want to know why that is the answer, then you can read http://dizwell.com/main/content/view/62/84/
January 17th, 2006 at 3:28 am
good article horward. note that moving the table to a readonly tablespace, revoking every rights from everybody does not prevent the owner from dropping his own table ;-)
The disable validate method does the trick in most case and is very handy.
January 17th, 2006 at 6:34 am
sorry for misspelling your name Howard, but sadly I cannot edit my comment :-(
January 17th, 2006 at 7:02 am
Connor pointed out here that if you add:
SQL> alter table T add constraint CK1 check ( 1=1) disable validate;
SQL> alter table T disable table lock;
the disable table lock will prevent the direct path load and the check will make the table effectively read only.
January 17th, 2006 at 1:22 pm
Laurent: dropping a table does not modify that table’s rows. Therefore, the table is still read-only, which I think you’ll find is the question that was asked.
However, the question did NOT ask “how do I make a table look sort-of read only good enough for most of the time in quite a handy fashion”. So the line about doing the trick “IN MOST CASES” is a rather important qualifier that means ‘disable novalidate’ does not answer the question.
Neither does the question read, “I want to add some spurious constraints to a table that might or might not affect execution plans, so please tell me how to go about doing this”.
And neither did the question ask, “How do I go about preventing users from dropping indexes, rebuilding indexes online, moving a table, enabling row movement, or shrinking and compacting a table”… all of which are unmentioned consequences of disabling the table lock in Connor’s fashion, and none of which he or Tom saw fit to mention as consequences.
So if the question was “How do I make something look read-only, without actually making it read-only, and also at the same time wreak havoc with my execution plans and my normal DBA functions”, then yeah… I guess the answers we have here will more or less suffice. But as to the question as actually posed, these are not technically correct answers, and lead to bad practice and poor outcomes.
We should also beware weasel words like “effectively”. Making something “effectively” read only is not the same as making something “actually” read only, and it is disingenuous to suggest they pretty much are. Next, we’ll have people saying, “I recovered my database”, when what they mean is “Actually, I lost half an hour’s worth of data botching a recovery”, and we’ll be expected to say, “Yeah, but it’s effectively been recovered”!
The question as written has one simple answer: “You can’t”. The business problem giving rise to the question has, however, many possible solutions, of which the enable validated constraint (and it doesn’t need to be a new one created for the occasion) is just one possible. Without qualifying the precise nature of that business problem, however, then simplistic answers are no answers at all.
January 18th, 2006 at 2:18 am
I guess the proper way to prevent an user from inserting/deleting/updating a table is granting only select on that table to the user
January 19th, 2006 at 7:26 pm
For the rest of us, here is why DISABLE VALIDATE makes the table immune to DML. Quoting Howard J. Rogers,
“Because if a constraint is disabled, it is not enforced… yet you want to “validate” it -in other words, you want to ensure that data conforms to the constraint’s requirements.
So if you want to make sure the data conforms to the constraint’s requirements, but you won’t let me check each data change as it is made to see that it conforms… I have only one choice, which is to lock the table out from all DML attempts.
It was invented in 8i to allow people to drop indexes in order to re-create them. Dropping an index that is associated with a constraint implies disabling the constraint… but that would then leave the table open to “bad” DML. The new constraint state was invented to allow you to disable the constraint enough to let you drop the index associated with it, but not permit dodgy DML to take place”
January 20th, 2006 at 12:17 pm
dizwell Says:
If you want to know why that is the answer, then you can read http://dizwell.com/main/content/view/62/84/
Howard you sure the link is right ???
(got the “Welcome to Dizwell!” page)
January 21st, 2006 at 8:39 am
Robert, here is a correct link:
http://dizwell.com/main/content/view/62/84/index.php?option=com_content&task=view&id=62&Itemid=84