jxc_hn 发表于 2016-1-27 08:22
为什么? 在包里的集合对象不能用table函数转成虚拟表?
系统包应该可以
SQL> explain plan for select * from dual;
已解释。
SQL> set lines 120 pages 50000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
已选择8行。
SQL> desc dbms_xplan
FUNCTION BUILD_PLAN_XML RETURNS XMLTYPE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
PLAN_ID NUMBER IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
PLAN_TAG VARCHAR2 IN DEFAULT
REPORT_REF VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
OUTLINE CLOB IN
USER_NAME VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN_AWR RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE1 NUMBER IN
PLAN_HASH_VALUE2 NUMBER IN
FUNCTION DIFF_PLAN_CURSOR RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
CURSOR_CHILD_NUM1 NUMBER IN
CURSOR_CHILD_NUM2 NUMBER IN
FUNCTION DIFF_PLAN_OUTLINE RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_TEXT CLOB IN
OUTLINE1 CLOB IN
OUTLINE2 CLOB IN
USER_NAME VARCHAR2 IN DEFAULT
FUNCTION DIFF_PLAN_SQL_BASELINE RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
BASELINE_PLAN_NAME1 VARCHAR2 IN
BASELINE_PLAN_NAME2 VARCHAR2 IN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_PLAN RETURNS CLOB
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION FORMAT_TIME_S RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NUM NUMBER IN
FUNCTION GET_PLANDIFF_REPORT_XML RETURNS XMLTYPE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
REPORT_REF VARCHAR2 IN DEFAULT
TID NUMBER IN
METHOD VARCHAR2 IN
FUNCTION PREPARE_PLAN_XML_QUERY RETURNS VARCHAR2
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
PLAN_QUERY VARCHAR2 IN
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER IN
FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN
FORMAT_FLAGS BINARY_INTEGER OUT
SQL>