oracle1422,奇怪的ORA-1422错误

发现bug总是喜欢扎堆，也许很长时间都碰不到bug，可能会在短时间内，碰到很多的bug。

这个bug又是在10.2.0.3上发现的。

检查后台alert文件时，发现一个JOB运行报错：

Errors in file /opt/oracle/admin/tradedb/bdump/tradedb1_j000_5103.trc:

ORA-12012:自动执行作业77出错ORA-01422:实际返回的行数超出请求的行数ORA-06512:在line 14

查询DBA_JOBS，发现这个JOB会运行一个存储过程。

开始没有太当回事，一般造成这个错误都是由于类似SELECT INTO的时候读取了多条记录造成的，比如：

SQL> DECLARE

2 V_NAME VARCHAR2(30);

3 BEGIN

4 SELECT TNAME INTO V_NAME FROM TAB;

5 END;

6 /

DECLARE

*第1行出现错误:

ORA-01422:实际返回的行数超出请求的行数ORA-06512:在line 4

但是在检查存储过程的时候，并没有发现类似的问题。而且，存储过程中进行了错误信息的捕获。

既然进行错误的捕获，而错误还能被抛出来，那么错误应该是出自存储过程的EXCEPTION部分。

但是EXCEPTION部分代码很简单，出错的可能性很小，更没有可能出现ORA-1422错误。

尝试执行这个存储过程，结果发现存储过程可以顺利执行。尝试DBMS_JOB.RUN运行这个失败的JOB，发现JOB也可以顺利运行。

于是开始怀疑这个问题是Oracle的bug，到metalink上一搜索，果然如此。

由于建立了一个系统登陆触发器，且触发器中对V$SESSION动态视图进行了查询,由于包含对SYSDATE的访问，造成了对JOB运行的阻塞，导致触发器中读取V$SESSION视图时出现ORA-1422错误。

Oracle给出的解决方法是删除或禁止系统触发器，或者在触发器中去掉SYSDATE函数。

Oracle在Doc ID:Note:402508.1中给出详细的描述。

根据Oracle给出的方法，原始的触发器：

SQL> CREATE TABLE LOGIN_LOGS

2 (

3 LOGIN_DATE DATE,

4 USERNAME VARCHAR2(30),

5 BG_JOB_ID VARCHAR2(64),

6 INSTANCE VARCHAR2(30),

7 IP_ADDRESS VARCHAR2(30),

8 OS_USER VARCHAR2(30),

9 TERMINAL VARCHAR2(30),

10 MACHINE VARCHAR2(64),

11 PROGRAM VARCHAR2(48),

12 MODULE VARCHAR2(48),

13 CLIENT_INFO VARCHAR2(64),

14 SERVICE_NAME VARCHAR2(64)

15 );

表已创建。

SQL> CREATE OR REPLACE TRIGGER TRI_ON_LOGON AFTER LOGON ON DATABASE

2 DECLARE

3 V_USER VARCHAR2(30);

4 V_OSUSER VARCHAR2(30);

5 V_TERMINAL VARCHAR2(30);

6 V_MACHINE VARCHAR2(64);

7 V_PROGRAM VARCHAR2(48);

8 V_MODULE VARCHAR2(48);

9 V_CLIENT_INFO VARCHAR2(64);

10 V_SERVICE_NAME VARCHAR2(64);

11 BEGIN

12 SELECT UPPER(USER) INTO V_USER FROM DUAL;

13 IF V_USER = 'YANGTK' THEN

14

15 SELECT OSUSER, TERMINAL, MACHINE, PROGRAM, MODULE, CLIENT_INFO, SERVICE_NAME

16 INTO V_OSUSER, V_TERMINAL, V_MACHINE, V_PROGRAM, V_MODULE, V_CLIENT_INFO, V_SERVICE_NAME

17 FROM V$SESSION

18 WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID');

19

20 INSERT INTO LOGIN_LOGS VALUES

21 (

22 SYSDATE,

23 V_USER,

24 SYS_CONTEXT('USERENV', 'FG_JOB_ID'),

25 SYS_CONTEXT('USERENV', 'INSTANCE'),

26 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),

27 V_OSUSER,

28 V_TERMINAL,

29 V_MACHINE,

30 V_PROGRAM,

31 V_MODULE,

32 V_CLIENT_INFO,

33 V_SERVICE_NAME

34 );

35 END IF;

36 END;

37 /

触发器已创建

应该修改为：

SQL> CREATE OR REPLACE TRIGGER TRI_ON_LOGON AFTER LOGON ON DATABASE

2 DECLARE

3 V_USER VARCHAR2(30);

4 V_OSUSER VARCHAR2(30);

5 V_TERMINAL VARCHAR2(30);

6 V_MACHINE VARCHAR2(64);

7 V_PROGRAM VARCHAR2(48);

8 V_MODULE VARCHAR2(48);

9 V_CLIENT_INFO VARCHAR2(64);

10 V_SERVICE_NAME VARCHAR2(64);

11 BEGIN

12 SELECT UPPER(USER) INTO V_USER FROM DUAL;

13 IF V_USER = 'YANGTK' THEN

14

15 SELECT OSUSER, TERMINAL, MACHINE, PROGRAM, MODULE, CLIENT_INFO, SERVICE_NAME

16 INTO V_OSUSER, V_TERMINAL, V_MACHINE, V_PROGRAM, V_MODULE, V_CLIENT_INFO, V_SERVICE_NAME

17 FROM V$SESSION

18 WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')

19 AND ROWNUM = 1;

20

21 INSERT INTO LOGIN_LOGS VALUES

22 (

23 SYSDATE,

24 V_USER,

25 SYS_CONTEXT('USERENV', 'FG_JOB_ID'),

26 SYS_CONTEXT('USERENV', 'INSTANCE'),

27 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),

28 V_OSUSER,

29 V_TERMINAL,

30 V_MACHINE,

31 V_PROGRAM,

32 V_MODULE,

33 V_CLIENT_INFO,

34 V_SERVICE_NAME

35 );

36 END IF;

37 END;

38 /

触发器已创建