使用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>