Welcome to plsql4all.blogspot.com SQL, MYSQL, ORACLE, TERADATA, MONGODB, MARIADB, GREENPLUM, DB2, POSTGRESQL.

Wednesday, 13 August 2014

Read Only Table in Oracle 11g

Read Only Table:-


In this post we will see new enhancement about read only tables in oracle released 11G.

In previous released of oracle database, we were able to grant the SELECT privileges and then only the users were able to query the table. But the owner of the table was able to make any changes to the table. Now, in oracle 11G we can make table as READ ONLY and no one can perform DML operation on the table. However the users who have SELECT privilege on that table can access the table.

Usage:-

When you need to provide only select privilege on particular table to all the users including the table owner, in this scenario you can make the table read only so no one can perform DML operation on the table but can still access the table.

Let’s see the example:-


Create table in oracle 10G and try to alter it as read only:- 


Insert some data into the table created above:-


Commit after inserting.

Let’s execute the select query and try to convert it as READ ONLY on the above created table named table1  (in oracle 10G). 


Above it is showing error while converting table table1 as read only and this is because the version below oracle 11G does not have this option.

Let’s try to do it in oracle 11G.

Create a table called table1:-


Insert data into the table created above:-


No comments:

Post a Comment

Please provide your feedback in the comments section above. Please don't forget to follow.