实验环境：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看看那个会话锁定了对象的方法来解决
