This content has been marked as final. Show 12 replies
Are you creating the view in your own schema or another? You need to be granted the CREATE ANY VIEW privilege in order to create the view in another schema.
If you are creating the view in your own schema, you need to be granted SELECT privileges on any underlying tables DIRECTLY, not through a role. This would explain why you are able to run the query in an SQL*Plus session (roles enabled), but not able to create a view using the same query.
I am creating view in my own schema
Is your own schema and the schema where you are trying to create the view are same?
Sorry, I updated my previous post...
I am trying to create view in my own schema referring a table in another schema the query for the view, when executed works fine, but create view statement generates error. I can create a view referring tables in my own schema.
Hope i am able to make my problem clear.
Thank you very much for your reply.
try with this --
Create or Replace View v1 As Select * from hr.employees;
Is your own schema and the schema where you are trying to create the view are same
I am unable to understand ur question what do u mean by "same"
Create or Replace View v1
Select * from hr.employees;
I have already tried this does not work
Where the table exists? Is it a remote database object? Or is it one the same scheama where u want to create your view? Suppose, u want to create view in SCOTT. And your table is in HR. Then the above query should work. If it is a remote table then u have to modify the above query as --
-Where tsn is a DB Link.
Create or Replace View v1 As Select * from hr.employees@tsn;
Hope, now u'll understand.
These are the prerequisite while creating a view:
(From the Oracle Docs)
To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.
To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.
The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.
To use the basic constructor method of an object type when creating an object view, one of the following must be true:
The object type must belong to the same schema as the view to be created.
You must have the EXECUTE ANY TYPE system privileges.
You must have the EXECUTE object privilege on that object type.
Please go through the link for details:
Dear Satyaki De.
I am not using remote objects objects are from same database but from different user schemas .I am investigating according to the reply of Mr. Anthony Wilson
Message was edited by: