【原创】删除plan_table表的奇怪问题

实验环境：WinXP SP2

数据库版本：10.2.0.1

今天在做实验的时候发现了一个奇怪的问题，大家可以先看看这个问题的表象：

查询的时候，plan_table没有任何问题

sys@TEST>select * from plan_table;

no rows selected

desc也没有任何问题

sys@TEST>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)

但是在drop时，会说找不到plan_table表了

sys@TEST>drop table plan_table;
drop table plan_table
           *
ERROR at line 1:
ORA-00942: table or view does not exist

而且在数据字典中也查不到plan_table表了

sys@TEST>select table_name from dba_tables where table_name='PLAN_TABLE';

no rows selected

产生这个问题的实验步骤如下：

在第一个会话中：

sys@TEST>set autotrace trace explain
sys@TEST>select * from dual;

Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------

在第二个会话中：

sys@TEST>set autotrace trace explain
sys@TEST>select * from dual;

Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------

然后在第二个会话中删除plan_table表

sys@TEST>drop table plan_table;

Table dropped.

通过查询执行计划，发现实际上在这种情况下（plan_table已经被删除，但有些会话还在进行autotrace操作），Oracle会自动将plan_table作为数据字典表plan_table$的同义词（绿色的部分）

sys@TEST>select * from plan_table;

Execution Plan
----------------------------------------------------------
Plan hash value: 103984305

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 | 11081 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| PLAN_TABLE$ |     1 | 11081 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

根据这个实验和http://space.itpub.net/498744/viewspace-264996中的实验，总结如下：

1、当前会话可以删除plan_table表（不论是否执行了autotrace操作）

2、如果有会话在执行autotrace操作，其他会话在没有执行autotrace操作的时候，是不能删除plan_table表的（plan_table被锁定）

3、如果有会话在执行autotrace操作，其他会话在执行autotrace操作的时候，可以删除plan_table表，其他正在执行autotrace操作的会话会自动使用plan_table$数据字典表

4、10g已经可以不用建立plan_table表了，会自动使用plan_table$数据字典表

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

转载于:http://blog.itpub.net/498744/viewspace-264997/