实验环境：WinXP SP2

数据库版本：10.2.0.1

今天在实验过程中发现autotrace是会锁定plan_table表的，具体实验步骤如下：

sys@ TEST>set autotrace on 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的话，就会报ORA-00054错误

sys@TEST>drop table plan_table;
drop table plan_table
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

通过查询v$locked_object表可以看出，在autotrace生成执行计划后会锁定plan_table表

sys@TEST>col object_name for a20
sys@TEST>select object_name,oracle_username,session_id from v$locked_object l,dba_objects d where l.object_id=d.object_id;

OBJECT_NAME          ORACLE_USERNAME                SESSION_ID
-------------------- ------------------------------ ----------
PLAN_TABLE           SYS                                   130

这时候在第一个会话中关闭autotrace也不能解除锁定

sys@TEST>set autotrace off

sys@TEST>select object_name,oracle_username,session_id from v$locked_object l,dba_objects d where l.object_id=d.object_id;

OBJECT_NAME          ORACLE_USERNAME                SESSION_ID
-------------------- ------------------------------ ----------
PLAN_TABLE           SYS                                   130

只有断开会话后，对plan_table表的锁定才可以解除

sys@TEST>conn / as sysdba
Connected.

sys@TEST>select object_name,oracle_username,session_id from v$locked_object l,dba_objects d where l.object_id=d.object_id;

no rows selected.

这时候就可以在第二个会话中删除plan_table表了

sys@TEST>drop table plan_table;

Table dropped.

所有在drop过程中报ORA-00054错误的问题，都可以通过查询v$locked_object看看那个会话锁定了对象的方法来解决