本文提供了解决在使用dbms_xplan.display_cursor功能时遇到的权限问题的方法，包括授权步骤和实际操作案例。通过为用户详细说明如何授权v$sql_plan、v$session、v$sql_plan_statistics_all以及v$sql表的select权限，确保能够成功运行该功能，避免常见的错误提示。
使用dbms_xplan.display_cursor function需要的几个权限：v$sql_plan,v$session,v$sql_plan_statistics_all，v$sql中的select权限！
如果没有会报：User has no SELECT privilege on v$sql_plan
User has no SELECT privilege on v$session
User has no SELECT privilege on v$sql_plan_statistics_all
User has no SELECT privilege on v$sql
所以下面就分别的授权
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> 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>
通过上面的授权就不会报错啦！兄台分享此文希望大家能少走弯路！！
扫一扫
7741
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
