I am facing a problem regarding creating views
When i execute simply query on a table in other schema it is executed fine but when i try to create view using same query it gives error "insufficient privileges" i can create view using table in my own schema
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 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.
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 --
Create or Replace View v1
Select * from hr.employees@tsn;
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: