Skip to Main Content
  • Questions
  • are WITH READ ONLY sand WITH CHECK OPTION syntaxes used for views and tables?


Question and Answer

Connor McDonald

Thanks for the question, Kris.

Asked: August 05, 2020 - 5:09 pm UTC

Answered by: Connor McDonald - Last updated: August 06, 2020 - 8:56 am UTC

Category: SQL - Version: Oracle 12c Release 1

Viewed 100+ times

You Asked


I am not sure whether or not WITH READ ONLY and WITH CHECK OPTION syntaxes used for views and tables only. I read on Oracle Help Center with the following link
It is said that:
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

I hope to receive your response.

and we said...

Yes, in my experience exclusively with views.

People often forget that views can be updatable and hence open up the chance of changing the base table, even if that view contains a join.

So most views should probably be created with the READ ONLY option by default, and if they are intended to be updated, then the WITH CHECK ensure people don't update "out of their area" so to speak.

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.