dbms_xplan.display_cursor不能正常显示处理

测试db环境今天无法正常显示dbms_xplan.display_cursor的结果，报错如下：
SQL> select count(*) from t1;

  COUNT(*)
----------
     49999

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

昨天还是OK的。
不过我昨天修改了$ORACLE_HOME/sqlplus/admin/glogin.sql，添加了几个项目：
[oracle@ora11g ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@ora11g admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- All rights reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script. is automatically run

SET LINESIZE 300
SET PAGES 500
SET LONG 999999
set serveroutput on format wrapped

column NAME format a30
column OWNER format a15
column VALUE format a50

惟有一些SET和COLUMN的命令，看起来都还是算正常，唯一可能出问题的在于set serveroutput这里。
关闭serveroutput，重新尝试：
SQL> set serveroutput off
SQL> select count(*) from t1;

  COUNT(*)
----------
     49999

SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |    65 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 49999 |    65   (0)| 00:00:01 |
-------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]


39 rows selected.

蛋疼，问题果然在这里。
从glogin.sql中把这行去掉吧。

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

转载于:http://blog.itpub.net/29154652/viewspace-774816/