怎样获取发生错误的会话信息 zt

http://www.laoxiong.net/how_to_get_session_occured_error.html[@more@]

某些Oracle错误，并不是总是伴随着产生trace文件，这些错误，对Oracle来说并不是严重的错误，比如像ORA-01555这样的错误。

我们可以设置一个事件，在发生错误时，产生一个Trace文件，事件通常使用下面的命令格式：

view plain copy to clipboard print ?

1. alter system set events ' trace name errorstack level ';
2. alter session set events ' trace name errorstack level ';

    alter system set events ' trace name errorstack level ';
    alter session set events ' trace name errorstack level ';

然而，使用alter system命令设置事件后，只会对新连接的会话有效。比如下面的测试：

view plain copy to clipboard print ?

1. 会话一：
2. [oracle@xty scripts]$ sqlplus test/test
4. SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 25 23:25:51 2009
6. Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
8. Connected to:
9. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
12. SQL> drop table t1;
14. Table dropped.
16. SQL>
17. SQL> create table t1 ( a number primary key);
19. Table created.
21. SQL> insert into t1 values (1);
23. 1 row created.
25. SQL> commit;
27. Commit complete.
29. 会话二：
31. alter system set events '1 trace name errorstack level 1';
33. 会话一：
34. SQL> insert into t1 values (1);
35. insert into t1 values (1)
36. *
37. ERROR at line 1:
38. ORA-00001: unique constraint (TEST.SYS_C005801) violated

会话一：
[oracle@xty scripts]$ sqlplus test/test

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 25 23:25:51 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table t1;

Table dropped.

SQL>          
SQL> create table t1 ( a number primary key);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit;

Commit complete.

会话二：

alter system set events '1 trace name errorstack level 1';

会话一：
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C005801) violated

此时检查user_dump_dest目录，没有相应的trace文件产生，如果我们再执行下面的动作：

view plain copy to clipboard print ?

1. SQL> conn test/test
2. Connected.
3. SQL> insert into t1 values (1);
4. insert into t1 values (1)
5. *
6. ERROR at line 1:
7. ORA-00001: unique constraint (TEST.SYS_C005801) violated

SQL> conn test/test
Connected.
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C005801) violated

就可以在user_dump_dest发现产生的trace文件。

那么发生错误的会话已经连接到数据库一段时间了，怎么得到这个会话在的信息？比如某个数据库，数据库中频繁地报下面的错误：

view plain copy to clipboard print ?

1. select sysdate create_time from dual
2. ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x09e5.0c3c77b1):
3. Wed Jul 22 11:17:51 2009
4. select g.*,m.* from Tb_Model m right outer join (select t.*,v.table_name from.....

select sysdate create_time from dual
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x09e5.0c3c77b1):
Wed Jul 22 11:17:51 2009
select g.*,m.*  from Tb_Model m right outer join (select t.*,v.table_name from.....

这个ORA-01555错误是非常怪异的，首先是查询DUAL表都会报错，其次，每次报错都是“Query Duration=0 sec, SCN: 0×09e5.0c3c77b1”，这里除了BUG,实在想不到其他的理由 。不过为了查明到底是哪个会话和哪个应用，是不是同一个会话引起，我们需要得到这个引起错误的会话的信息。

这里，我们可以用触发器。以SYS用户执行下面的代码：

view plain copy to clipboard print ?

1. create table t ( msg varchar2(4000));
3. create or replace trigger xj_error
4. after servererror on database
5. declare
6. l_sql_text ora_name_list_t;
7. l_n number;
8. begin
9. if ( is_servererror(1555) )
10. then
11. insert into t values ( 'ora_sysevent = ' || ora_sysevent );
12. insert into t values ( 'ora_login_user = ' || ora_login_user );
13. insert into t values ( 'ora_server_error = ' || ora_server_error(1) );
15. l_n := ora_sql_txt( l_sql_text );
16. for i in 1 .. l_n
17. loop
18. insert into t values ( 'l_sql_text(' || i || ') = ' || l_sql_text(i) );
19. end loop;
21. insert into t
22. select 'sid:' || sid || ' machina:' || machine || ' program:' || program || ' module:' || module from v$session where sid=(select sid from v$mystat where rownum=1);
23. insert into t values ('IP:' || sys_context('USERENV', 'IP_ADDRESS') || ' HOST:' || sys_context('USERENV', 'HOST'));
24. end if;
25. end;
26. /

create table t ( msg varchar2(4000));

create or replace trigger xj_error
after servererror on database
declare
    l_sql_text ora_name_list_t;
    l_n        number;
begin
    if ( is_servererror(1555) )
    then
        insert into t values ( 'ora_sysevent = ' || ora_sysevent );
        insert into t values ( 'ora_login_user = ' || ora_login_user );
        insert into t values ( 'ora_server_error = ' || ora_server_error(1) );

        l_n := ora_sql_txt( l_sql_text );
        for i in 1 .. l_n
        loop
            insert into t values ( 'l_sql_text(' || i || ') = ' || l_sql_text(i) );
        end loop;

        insert into t
        select 'sid:' || sid || ' machina:' || machine || ' program:' || program  || ' module:' || module from v$session where sid=(select sid from v$mystat where rownum=1);
        insert into t values ('IP:' || sys_context('USERENV', 'IP_ADDRESS') || ' HOST:' || sys_context('USERENV', 'HOST'));
    end if;
end;
/

过一段时间，然后可以从SYS.T表可以得到：

view plain copy to clipboard print ?

1. SQL> select * from t;
3. MSG
4. --------------------------------------------------------------------
5. ora_sysevent = SERVERERROR
6. ora_login_user = XXX
7. ora_server_error = 1555
8. l_sql_text(1) = select g.*,m.* from Tb_Model m right outer join (select t.*,v.t
9. ....
10. sid:434 machine :temp1 program: module:
11. IP:xxx.xxx.xxx.xxx HOST: temp1

SQL> select * from t;

MSG
--------------------------------------------------------------------
ora_sysevent = SERVERERROR
ora_login_user = XXX
ora_server_error = 1555
l_sql_text(1) = select g.*,m.*  from Tb_Model m right outer join (select t.*,v.t
....
sid:434 machine :temp1 program: module:
IP:xxx.xxx.xxx.xxx HOST: temp1

通过得到的会话信息，得知是一个Web应用的连接，可以安全地KILL之。将这个会话KILL掉，错误不再出现。

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

转载于:http://blog.itpub.net/82387/viewspace-1024799/