精选国内外互联网行业最新文章及报告,让网友获得最新的海内外互联网动态
当前位置:主页 > 网络编程 > 数据库技术 >

User has no SELECT privilege on V$SESSION

2015-01-21 09:09 来源: 编辑:admin
User has no SELECT privilege on V$SESSION   使用dbms_xplan.display_cursor function的时候发现如下错误。 SQL> select * from amy_dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- User has no SELECT privilege on V$SESSION SQL>  然后查看官网发现这么几句话: This package runs with the privileges of the calling user, not the package owner (SYS). The table functionDISPLAY_CURSOR requires to have select privileges on the following fixed views:V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. 也就是说要有,v$sql_plan,v$session,v$sql_plan_statistics_all权限。 如下: SQL> conn sys/root as sysdba Connected. SQL> grant select on v_$sql_plan to rhys; Grant succeeded. SQL> grant select on v_$session to rhys; Grant succeeded. SQL> grant select on v_$sql_plan_statistics_all to rhys; Grant succeeded. SQL> conn rhys/amy Connected. SQL> select * from dept; select * from dept * ERROR at line 1: ORA-00942: table or view does not exist   SQL> select * from amy_dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- User has no SELECT privilege on V$SESSION SQL> 依然出错,咋回事?然后继续看联机手册,又发现了这么一句话: To use the DISPLAY_CURSOR functionality, the calling user must haveSELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL,V$SQL and V$SQL_PLAN, otherwise it shows an appropriate error message. 呵呵。那么这就明了,要想使用display_cursor那么必须要有对v$sql,v$sql_plan_statistics_all,v$session,v$sql_pan这四个视图的权限。先看看rhys账户有哪些权限吧: SQL> select  2 substr(grantee,1,20), 3 substr(owner,1,20), 4 substr(table_name,1,20), 5 substr(grantor,1,20), 6 substr(privilege,1,30), 7 substr(grantable,1,20), 8 substr(hierarchy,1,20) 9 from user_tab_privs;   SUBSTR(GRANTEE,1,20) SUBSTR(OWNER,1,20) SUBSTR(TABLE_NAME,1, SUBSTR(GRANTOR,1,20) SUBSTR(PRIVILEGE,1,30) SUB SUB -------------------- -------------------- -------------------- -------------------- ------------------------------ --- --- RHYS SYS V_$SESSION SYS SELECT NO NO RHYS SYS V_$SQL_PLAN SYS SELECT NO NO RHYS SYS V_$SQL_PLAN_STATISTI SYS SELECT NO NO   SQL>  SQL> conn sys/root as sysdba Connected. SQL> grant select on v_$sql to rhys;   Grant succeeded.   SQL> conn rhys/amy Connected. SQL> select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID dyk4dprp70d74, child number 0 ------------------------------------- SELECT DECODE('A','A','1','2') FROM DUAL   Plan hash value: 1388734953   ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 2 (100)| | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | -----------------------------------------------------------------   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     13 rows selected.   SQL>   
标签
你喜欢的文章
返回首页
扫描微信
返回顶部