dbms_xplan.display_cursor函数可以用来获取sql执行路径,不带任何参数可以获取当前session的最后一条sql语句的执行路径,或者可以指定参数获取指定sql的执行路径。
普通数据库用户在调用该函数时,若没有赋予相关表的查询权限,将会报错,如下:
此时可知该用户没有查询v$session视图的权限,需要赋予v_$session视图的权限
再次执行该语句,发现依然报错,原来调用该函数,还需要赋予查询v$sql、v$sql_plan等表的权限
可知,普通数据库用户调用dbms_xplan.display_cursor函数,需赋予表v$session、v$sql、v$sql_plan的查询权限。
普通数据库用户在调用该函数时,若没有赋予相关表的查询权限,将会报错,如下:
点击(此处)折叠或打开
- SQL> select count(*) from t;
-
- COUNT(*)
- ----------
- 99296
-
- SQL> select * from table(dbms_xplan.display_cursor);
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- User has no SELECT privilege on V$SESSION
点击(此处)折叠或打开
- SQL> grant select on v_$session to test;
-
- Grant succeeded.
再次执行该语句,发现依然报错,原来调用该函数,还需要赋予查询v$sql、v$sql_plan等表的权限
点击(此处)折叠或打开
- SQL> select * from table(dbms_xplan.display_cursor);
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- User has no SELECT privilege on V$SESSION
点击(此处)折叠或打开
- SQL> grant select on v_$sql to test;
-
- Grant succeeded.
-
- SQL> grant select on v_$sql_plan to test;
-
- Grant succeeded.
点击(此处)折叠或打开
- SQL> select * from table(dbms_xplan.display_cursor);
-
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- SQL_ID 3694ctwp1817g, child number 1
- -------------------------------------
- select * from table(dbms_xplan.display_cursor)
-
- Plan hash value: 3713220770
-
- ----------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 29 (100)| |
- | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 2 | 4 | 29 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------------------
-
- Note
- -----
- - cardinality feedback used for this statement
-
-
- 17 rows selected.
可知,普通数据库用户调用dbms_xplan.display_cursor函数,需赋予表v$session、v$sql、v$sql_plan的查询权限。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24420807/viewspace-1343818/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24420807/viewspace-1343818/