Home » SQL & PL/SQL » SQL & PL/SQL » Permission to create views on V$ (11.1.0.7.0, Windows Server 2008)
Permission to create views on V$ [message #432662] Wed, 25 November 2009 10:10 Go to next message
db33
Messages: 21
Registered: July 2009
Junior Member
Hi,

I was trying to incorporate some of the session lookup stuff into my default dba account. I can execute the sql, but it appears I cannot create a view for it. I have the dba role. Is there something special about creating view of the v$ views?


create view all_sessions as
select s.sid,s.serial#,s.username,s.osuser,substr(sa.sql_text,1,80) sql_txt from v$session s, v$sqlarea sa where s.sql_address = sa.address(+) and s.sql_hash_value = sa.hash_value(+);

ERROR at line 2:
ORA-01031: insufficient privileges


If I run the above as sysdba, it works. Just not an account with the role of dba or the above as 'create view pdba.allsessions..'.
Thanks.
Re: Permission to create views on V$ [message #432663 is a reply to message #432662] Wed, 25 November 2009 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
as sysdba
do
GRANT CREATE VIEW TO <YOUR_USER>;
GRANT SELECT_CATALOG_ROLE TO <YOUR_USER>;

[Updated on: Wed, 25 November 2009 10:15]

Report message to a moderator

Re: Permission to create views on V$ [message #432664 is a reply to message #432662] Wed, 25 November 2009 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before using a statement maybe you should read the documentation, especially the prerequisites:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510
Quote:
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.

Regards
Michel

[Updated on: Wed, 25 November 2009 10:15]

Report message to a moderator

Re: Permission to create views on V$ [message #432665 is a reply to message #432664] Wed, 25 November 2009 10:23 Go to previous message
db33
Messages: 21
Registered: July 2009
Junior Member
That makes sense. I didn't catch the not through a role prereq.
Thanks.
Previous Topic: inserting more than one record in a single transaction
Next Topic: Problems in Updating Table
Goto Forum:
  


Current Time: Fri Dec 09 08:11:24 CST 2016

Total time taken to generate the page: 0.09774 seconds