使用alter session set current_schema=scott用explain plan for查看scott用户的sql

某Q友问题

   explain没有思路，除了切换到sql的用户下面


   我知道用户也不知道密码


   这也切不过去啊

结论

1，学会查看官方手册，前提就是要多看多思考，其码熟悉官方手册的知识结构构成，方可针对性查阅资料
2, alter session set current_schema可以切换到对应指定的用户，相当于在指定用户进行查看相关操作

测试

SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> show user
USER is "SCOTT"
SQL> explain plan for select count(a) from t_plan;


Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4286448300


-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_PLAN |     1 |    13 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------


   1 - SEL$1
   2 - SEL$1 / T_PLAN@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T_PLAN"@"SEL$1")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */


Note
-----
   - dynamic sampling used for this statement (level=2)


33 rows selected.


SQL> 




----现在我们回到开头的Q友问题，无非就是想让在SYS用户这个会话可以查看SCOTT用户的创建的sql执行计划,所以我想肯定是到官方手册  Oracle? Database SQL Language Reference
去找，找哪块呢，是和会话相关，那么只能是alter session,经查阅发现
alter session set current_schema可以达到这个目标，现摘录相关信息于此，供大家参考：


Syntax:


CURRENT_SCHEMA = schema
The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.


This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.








SQL> show user
USER is "SYS"




SQL> explain plan for select count(a) from t_plan;
explain plan for select count(a) from t_plan
                                      *
ERROR at line 1:
ORA-00942: table or view does not exist




SQL> alter session set current_schema=scott;


Session altered.


SQL> explain plan for select count(a) from t_plan;


Explained.


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4286448300


-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_PLAN |     1 |    13 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------


   1 - SEL$1
   2 - SEL$1 / T_PLAN@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T_PLAN"@"SEL$1")


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */


Note
-----
   - dynamic sampling used for this statement (level=2)


33 rows selected.


SQL> 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1872040/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1872040/