一步一脚印

今天测试一个存储过程的时候无意发现了一个10g sqlplus 的bug.

数据库版本信息：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

先创建一个过程，证明权限是没问题的：
SQL> create or replace procedure p_test is
2 begin
null;
3 4 end;
5 /

Procedure created.

--打开autotrace，执行后关闭autotrace
SQL> set autotrace trace
SQL> select 1 from dual;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
508 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off

--此时再次创建过程会提示权限不足
SQL> create or replace procedure p_test is
2 begin
3 null;
4 null;
5 end;
6 /
create or replace procedure p_test is
*
ERROR at line 1:
ORA-01031: insufficient privileges

遇到这种情况，只能重新连接，否则执行什么都会报权限不足。

--查了一下，后来修改的过程并没有成功
SQL> select text from user_source where name='P_TEST';

TEXT
--------------------------------------------------------------------------------
procedure p_test is
begin
null;
end;

SQL> show user
USER is "SYS"

简单总结一下出发这个bug的条件：
1、以sys用户登录
2、在打开、并关闭autotrace开关后执行SQL
3、数据库版本是10g（只在10201测试过）