ORACLE查看一个视图是由哪些表而来

核心sql
explain plan for (select * from v1);
select * from table(dbms_xplan.display());

SQL解析
dbms_xplan.display()相当于一个report，是由plan_table而来，
也就是说explain生成执行计划之后，数据存入plan_table，然后由plan_table生成dbms_xplan.display() report，我们在查看时，可以直接查看这个report
report由plan_table而来的依据：
dbms_xplan.display()是一个加密的，可以通过unwrap工具解密查看，发现是从plan_table而来
dbms_xplan是一个包，用来查看执行计划的包
dbms_xplan信息来自于db_source
Select text from db_source where XXX=’dbms_xplain’；

实验

1. scott用户下创建一个视图
   Create view v1 as
   Select empno,dname from emp,dept where emp.deptno=dept.deptno;
   ***没有grant权限的话切换sys用户赋予权限

2. 生成执行计划并展示report
   scott@ORCL>explain plan for (select * from v1);
   scott@ORCL>select * from table(dbms_xplan.display());

结论显示DEPT，EMP这两张表构成了v1视图

scott@ORCL>explain plan for(select * from v1);

Explained.

scott@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   280 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   280 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |    98 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 rows selected.

延伸
上面我们说dbms_xplan.display() report是由plan_table而来的，那么接下来就简单介绍一下plan_table
首先看看plan_table里都有哪些字段

scott@ORCL>desc plan_table;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 STATEMENT_ID                                                                                                               VARCHAR2(30)
 PLAN_ID                                                                                                                    NUMBER
 TIMESTAMP                                                                                                                  DATE
 REMARKS                                                                                                                    VARCHAR2(4000)
 OPERATION                                                                                                                  VARCHAR2(30)
 OPTIONS                                                                                                                    VARCHAR2(255)
 OBJECT_NODE                                                                                                                VARCHAR2(128)
 OBJECT_OWNER                                                                                                               VARCHAR2(30)
 OBJECT_NAME                                                                                                                VARCHAR2(30)
 OBJECT_ALIAS                                                                                                               VARCHAR2(65)
 OBJECT_INSTANCE                                                                                                            NUMBER(38)
 OBJECT_TYPE                                                                                                                VARCHAR2(30)
 OPTIMIZER                                                                                                                  VARCHAR2(255)
 SEARCH_COLUMNS                                                                                                             NUMBER
 ID                                                                                                                         NUMBER(38)
 PARENT_ID                                                                                                                  NUMBER(38)
 DEPTH                                                                                                                      NUMBER(38)
 POSITION                                                                                                                   NUMBER(38)
 COST                                                                                                                       NUMBER(38)
 CARDINALITY                                                                                                                NUMBER(38)
 BYTES                                                                                                                      NUMBER(38)
 OTHER_TAG                                                                                                                  VARCHAR2(255)
 PARTITION_START                                                                                                            VARCHAR2(255)
 PARTITION_STOP                                                                                                             VARCHAR2(255)
 PARTITION_ID                                                                                                               NUMBER(38)
 OTHER                                                                                                                      LONG
 OTHER_XML                                                                                                                  CLOB
 DISTRIBUTION                                                                                                               VARCHAR2(30)
 CPU_COST                                                                                                                   NUMBER(38)
 IO_COST                                                                                                                    NUMBER(38)
 TEMP_SPACE                                                                                                                 NUMBER(38)
 ACCESS_PREDICATES                                                                                                          VARCHAR2(4000)
 FILTER_PREDICATES                                                                                                          VARCHAR2(4000)
 PROJECTION                                                                                                                 VARCHAR2(4000)
 TIME                                                                                                                       NUMBER(38)
 QBLOCK_NAME                                                                                                                VARCHAR2(30)

scott@ORCL>

对比较重要的几个字段进行说明

scott@ORCL>Select object_owner,object_name,object_type from plan_table;

OBJECT_OWNER                   OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
SCOTT                          DEPT                           TABLE
SCOTT                          PK_DEPT                        INDEX (UNIQUE)
SCOTT                          EMP                            TABLE

OBJECT_OWNER： 对象用户
OBJECT_NAME：对象（例如table 名）
OBJECT_TYPE：是table类型还是其他类型

这里你也可以指定将OBJECT_TYPE=‘TABLE’条件来进行筛选，即可查询出你想要的table。