在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清空
以上仅代表个人观点
There’s noticeably a bundle to learn about this. I assume you made certain nice factors in options also.