首页 » ORACLE 9i-23c » 11g sqlplus errorlogging 记录跟踪error日志

11g sqlplus errorlogging 记录跟踪error日志

在ORACLE11G开始，sqlplus 提供了几个非常有趣的功能接口，比如记录执行的错误日志有sqlplus 的执行文件自动记录到数据库的表中，从而替代了glogin.sql的实现方法

在以前的版本中，如果sql或plsql 出现了错误，最常见的方法，就是到sqlplus里去执行，然后通过show error显示提示的err code,及大致错误位置，而且还没办法查看历史错误记录，但11G中可以实现，它是把错误信息自动记录到当前用户下的一个表中，而且不会自动删除，默认的表名SPERRORLOG，也可以指定自己的表名替换默认表名

下面我通过几个例子来说明这一特点
转载请声明出处http://www.anbob.com/?p=1102

[oracle@orazhang oracle11g]$ sqlplus -v

SQL*Plus: Release 11.2.0.1.0 Production

[oracle@orazhang oracle11g]$ ora

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 9月 14 16:05:59 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

sys@ANBOB> show errorlogging
errorlogging is OFF
sys@ANBOB> set errorlogging on
sys@ANBOB> show errorlogging
errorlogging is ON TABLE SYS.SPERRORLOG
sys@ANBOB> desc sperrorlog;
 Name                       Null?    Type
 -------------------------- -------- ---------------------------
 USERNAME                            VARCHAR2(256)
 TIMESTAMP                           TIMESTAMP(6)
 SCRIPT                              VARCHAR2(1024)
 IDENTIFIER                          VARCHAR2(256)
 MESSAGE                             CLOB
 STATEMENT                           CLOB

sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> show errorlogging
errorlogging is OFF
anbob@ANBOB> set errorlogging on;
anbob@ANBOB> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG

anbob@ANBOB> conn system/oracle
Connected.
system@ANBOB> COL OBJECT_NAME FOR A20
system@ANBOB>select object_name,object_type from dba_objects where object_name='SPERRORLOG';   

OBJECT_NAME          OBJECT_TYPE         OWNER
-------------------- ------------------- ------------------------------
SPERRORLOG           TABLE               SYS
SPERRORLOG           TABLE               ANBOB

system@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> show errorlogging
errorlogging is OFF

anbob@ANBOB> set errorlogging on
anbob@ANBOB> select dbms_metadata.getddl('TABLE','SPERRORLOG') FROM dual;
select dbms_metadata.getddl('TABLE','SPERRORLOG') FROM dual
       *
ERROR at line 1:
ORA-00904: "DBMS_METADATA"."GETDDL": 标识符无效

anbob@ANBOB> select dbms_metadata.get_ddl('TABLE','SPERRORLOG') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','SPERRORLOG')
--------------------------------------------------------------------------------

  CREATE TABLE "ANBOB"."SPERRORLOG"
   (    "USERNAME" VARCHAR2(256),
        "TIMESTAMP" TIMESTAMP (6),
        "SCRIPT" VARCHAR2(1024),
        "IDENTIFIER" VARCHAR2(256),
        "MESSAGE" CLOB,
        "STATEMENT" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("MESSAGE") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAU
LT))
 LOB ("STATEMENT") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

anbob@ANBOB> select * from sperrorlog;

USERNAME   TIMESTAMP                      SCRIPT               IDENTIFIER MESSAGE                        STATEMENT
---------- ------------------------------ -------------------- ---------- ------------------------------ ------------------------------
ANBOB      14-9月 -11 04.16.32.000000 下                                  ORA-00904: "DBMS_METADATA"."GE select dbms_metadata.getddl("T
           午                                                             TDDL": 标识符无效              ABLE","SPERRORLOG") FROM dual

-------此时另开一session 2 ----
[oracle@orazhang oracle11g]$ ora

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 9月 14 16:20:54 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

sys@ANBOB> select * from anbob.sperrorlog;

no rows selected

------------回到session 1-------
anbob@ANBOB> commit;

Commit complete.
------------session 2--------
sys@ANBOB> select * from anbob.sperrorlog;

USERNAME   TIMESTAMP                      SCRIPT               IDENTIFIER MESSAGE                        STATEMENT
---------- ------------------------------ -------------------- ---------- ------------------------------ ------------------------------
ANBOB      14-9月 -11 04.16.32.000000 下                                  ORA-00904: "DBMS_METADATA"."GE select dbms_metadata.getddl("T
           午                                                             TDDL": 标识符无效              ABLE","SPERRORLOG") FROM dual

对sperrorlog表修改又会怎样？
anbob@ANBOB> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG
anbob@ANBOB> alter table sperrorlog add flag int;

Table altered.

anbob@ANBOB> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG
anbob@ANBOB> select * from sperrorlog;

USERNAME   TIMESTAMP                      SCRIPT               IDENTIFIER MESSAGE                        STATEMENT                            FLAG
---------- ------------------------------ -------------------- ---------- ------------------------------ ------------------------------ ----------
ANBOB      14-9月 -11 04.16.32.000000 下                                  ORA-00904: "DBMS_METADATA"."GE select dbms_metadata.getddl("T
           午                                                             TDDL": 标识符无效              ABLE","SPERRORLOG") FROM dual

ANBOB      14-9月 -11 04.29.47.000000 下                                  ORA-00942: 表或视图不存在      select * from noexiststable
           午
anbob@ANBOB> alter table sperrorlog add flag ints;
alter table sperrorlog add flag ints
                           *
ERROR at line 1:
ORA-01430: 表中已存在要添加的列
ERROR:
ORA-00947: 没有足够的值

SP2-1519: Unable to write to the error log table ANBOB.SPERRORLOG

anbob@ANBOB> show errorlogging
errorlogging is OFF
anbob@ANBOB> set errorlogging on
anbob@ANBOB> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG
anbob@ANBOB> alter table sperrorlog drop column flag;

Table altered.

anbob@ANBOB> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG

anbob@ANBOB> alter table testcomp add test ints;
alter table testcomp add test ints
                              *
ERROR at line 1:
ORA-00902: 无效数据类型

anbob@ANBOB> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG
anbob@ANBOB> select * from sperrorlog;

USERNAME   TIMESTAMP                      SCRIPT               IDENTIFIER MESSAGE                        STATEMENT
---------- ------------------------------ -------------------- ---------- ------------------------------ ------------------------------
ANBOB      14-9月 -11 04.16.32.000000 下                                  ORA-00904: "DBMS_METADATA"."GE select dbms_metadata.getddl("T
           午                                                             TDDL": 标识符无效              ABLE","SPERRORLOG") FROM dual

ANBOB      14-9月 -11 04.29.47.000000 下                                  ORA-00942: 表或视图不存在      select * from noexiststable
           午

ANBOB      14-9月 -11 04.39.05.000000 下                                  ORA-00902: 无效数据类型        alter table testcomp add test
           午                                                                                            ints

那连11Gclient连接10GR 的库可不可以呢？
anbob@ANBOB> conn anbob/anbob@192.168.3.229:1528/orcl
Connected.
anbob@ORCL> select * from v$version;

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

anbob@ORCL> set errorlogging on
anbob@ORCL> show errorloging
SP2-0735: unknown SHOW option beginning "errorlogin..."
anbob@ORCL> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG
anbob@ORCL> select 1/0 from dual;
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

anbob@ORCL> select * from sperrorlog;

USERNAME   TIMESTAMP                      SCRIPT               IDENTIFIER MESSAGE                        STATEMENT
---------- ------------------------------ -------------------- ---------- ------------------------------ ------------------------------
ANBOB      14-9月 -11 04.45.05.000000 下                                  SP2-0735: unknown SHOW option  show errorloging
           午                                                             beginning "errorlogin..."

ANBOB      14-9月 -11 04.45.28.000000 下                                  ORA-01476: divisor is equal to select 1/0 from dual
           午                                                              zero

anbob@ORCL> set errorlogging on identifier '10g test';
anbob@ORCL> show errorlogging
errorlogging is ON TABLE ANBOB.SPERRORLOG IDENTIFIER 10g test
anbob@ORCL> select * from xxooxx;
select * from xxooxx
              *
ERROR at line 1:
ORA-00942: table or view does not exist

anbob@ORCL> select * from sperrorlog;

USERNAME   TIMESTAMP                      SCRIPT               IDENTIFIER MESSAGE                        STATEMENT
---------- ------------------------------ -------------------- ---------- ------------------------------ ------------------------------
ANBOB      14-9月 -11 04.45.05.000000 下                                  SP2-0735: unknown SHOW option  show errorloging
           午                                                             beginning "errorlogin..."

ANBOB      14-9月 -11 04.45.28.000000 下                                  ORA-01476: divisor is equal to select 1/0 from dual
           午                                                              zero

ANBOB      14-9月 -11 04.48.45.000000 下                       10g test   ORA-00942: table or view does  select * from xxooxx
           午                                                             not exist

anbob@ANBOB> set errorlogging on truncate
anbob@ANBOB> select * from sperrorlog;

no rows selected

note:
1，errorlogging默认是关闭的
2，错误信息类似一条dml，发生错误后如果没有commit提交，其它session是无法查看到的
3，修改sperrorlog表错误也会使errorlogging关闭
4，11G客户端修改低于它的版本也可以启动这个功能
5，可以指定identifier在以后查询时方便
6，errorlog不会自动删除，可以通过set errorlogging on truncate清空

以上仅代表个人观点

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Lorna Carouthers | #1
    2011-12-21 at 05:42

    There’s noticeably a bundle to learn about this. I assume you made certain nice factors in options also.

上一篇：

下一篇：