Learning is not a spectator sport

March 13, 2014

views in 12c

Filed under: Uncategorized — connormcdonald @ 7:50 pm

Observed an interesting idiosyncracy in the creation of views in 12c (this is in a pluggable database, but not confirmed whether this is related or not).  This database was upgraded from 11.2 without incident…until we came to replace one of the existing views.

SQL> create or replace
2 view EXISTING_VIEW_NAME
3 as select * from other_schema.account;
ERROR at line 3:
ORA-01720: grant option does not exist for 'OTHER_SCHEMA.ACCOUNT'
*

Now that struck us as odd, because this was a script from our source code control repository, which had no previous entries about grants for the OTHER_SCHEMA.ACCOUNT table.  So then I tried this:

SQL> create or replace
2 view NEW_VIEW_NAME
3 as select * from other_schema.account;

View created.

so there doesn’t appear to be the need for the grant option.  Similarly, dropping the original view also solves the problem.

SQL> drop view EXISTING_VIEW_NAME;

View dropped.

SQL> create or replace
2 view EXISTING_VIEW_NAME
3 as select * from other_schema.account;

View created.

So when you get errors regarding grants in 12c, perhaps just double check to make sure you really need it.

About these ads

2 Comments »

  1. This is strange, i’ve tried to reproduce the problem but i’m not able to reproduce it…

    Comment by Cristian — April 9, 2014 @ 9:28 pm

  2. We experienced the same issue in version 11.2.0.4 as well.

    Comment by riyaz — April 20, 2014 @ 12:09 am


RSS feed for comments on this post. TrackBack URI

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

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: