oracle 游标异常处理的select怎么处理异常
点击联系发帖人
时间：2016-12-04 15:35
Oracle监听不定期出现异常故障处理_数据库技术_Linux公社-Linux系统门户网站
你好，游客
Oracle监听不定期出现异常故障处理
来源：Linux社区&
作者：passion_of_data
环境：操作系统为：SunOS 5.10数据库版本： RAC 11.2.0.3.0该主机上有2个库，一套RAC的节点1在上面，还有另外一个库，之所以使用的是DB下的监听而没用GRID下的监听，是为了避免停掉CRS时影响另外一个库的使用。
故障现象：数据库监听不定期出现异常 ,从应用tnsping数据库，时间花费很长甚至连不上
故障分析处理过程：
从应用主机tnsping如下：
racdb1_scenemon$tnsping racdb_new&TNS Ping Utility for Solaris: Version 11.2.0.3.0 - Production on 19-2月 -:23&Copyright (c) , Oracle. All rights reserved.&已使用的参数文件:
已使用 TNSNAMES 适配器来解析别名&尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.2)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))&OK (60000 毫秒)
Oracle监听之动态监听与静态监听特点
Oracle 11g RAC 环境下单实例非缺省监听及端口配置
Oracle 监听器日志配置与管理
Oracle错误- ORA-12514：TNS：无监听程序
ORA-12514 监听错误解决
Oracle监听器出现的6种连接问题及其解决方法
Oracle LISTENER 未监听到Oracle实例问题解决
从数据库服务器上查看监听状态：
$ lsnrctl status&LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 19-FEB-:58&Copyright (c) , Oracle. All rights reserved.&Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))&
TNS-12535: TNS:operation timed out&TNS-12560: TNS:protocol adapter error&TNS-00505: Operation timed out& &
将监听进程予以kill并重启监听：
$ ps -ef |grep tns&oracle
0 15:45:15 pts/10 0:00 grep tns&oracle
Nov 28 ? 6:08 /oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER -inherit&oracle
Jan 24 ? 4447:11 /oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER -inherit&$ &$ kill -9 9488&$ lsnrctl start&LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 19-FEB-:22&Copyright (c) , Oracle. All rights reserved.&Starting /oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...&TNSLSNR for Solaris: Version 11.2.0.3.0 - Production&Log messages written to /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml&Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))&Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))&STATUS of the LISTENER&------------------------&Alias LISTENER&Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production&Start Date 19-FEB-:22&Uptime 0 days 0 hr. 0 min. 0 sec&Trace Level off&Security ON: Local OS Authentication&SNMP OFF&Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml&Listening Endpoints Summary...&(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))&The listener supports no services&The command completed successfully& &
检查listener.log如下：
17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41603)) * establish * racdb * 0&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41659)) * establish * racdb * 12518&TNS-12518: TNS:listener could not hand off client connection&TNS-12560: TNS:protocol adapter error&TNS-00530: Protocol adapter error&Solaris Error: 24: Too many open files &&&&&&&&&&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41652)) * establish * racdb * 0&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41669)) * establish * racdb * 0&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41682)) * establish * racdb * 12518&TNS-12518: TNS:listener could not hand off client connection&TNS-12560: TNS:protocol adapter error&TNS-00530: Protocol adapter error&Solaris Error: 24: Too many open files &&&&&&&&&&
Too many open files意味着Maximum Number Of Open Files Per Process 达到了上限。因此listener hang住的原因可能是该limit设置过小，进一步查看硬件限制如下：
$ ulimit -Ha&time(seconds) unlimited&file(blocks) unlimited&data(kbytes) unlimited&stack(kbytes) unlimited&coredump(blocks) unlimited&nofiles(descriptors) 65536&memory(kbytes) unlimited
查看软件限制如下：
$ ulimit -Sa&time(seconds) unlimited&file(blocks) unlimited&data(kbytes) unlimited&stack(kbytes) 8192&coredump(blocks) unlimited&nofiles(descriptors) 256&memory(kbytes) unlimited
查看/etc/system如下：
*ident& "@(#)system& &
1.18& & 97/06/27 SMI" /* SVR4 1.5 */
* SYSTEM SPECIFICATION FILE
Set the search path for modules.& This has a format similar to the
csh path variable. If the module isn't found in the first directory
it tries the second and so on. The default is /kernel /usr/kernel
*& & & & & & &
moddir: /kernel /usr/kernel /other/modules
* root device and root filesystem configuration:
The following may be used to override the defaults provided by
the boot program:
rootfs:& & & &
Set the filesystem type of the root.
rootdev:& & & & Set the root device.& This should be a fully
*& & & & & & & & & & &
expanded physical pathname.& The default is the
*& & & & & & & & & & &
physical pathname of the device where the boot
*& & & & & & & & & & &
program resides.& The physical pathname is
*& & & & & & & & & & &
highly platform and configuration dependent.
*& & & & & & &
rootfs:ufs
*& & & & & & &
rootdev:/sbus@1,f8000000/esp@0,800000/sd@3,0:a
(Swap device configuration should be specified in /etc/vfstab.)
* exclude:
Modules appearing in the moddir path which are NOT to be loaded,
even if referenced. Note that `exclude' accepts either a module name,
or a filename which includes the directory.
*& & & & & & &
exclude: win
*& & & & & & &
exclude: sys/shmsys
* forceload:
Cause these modules to be loaded at boot time, (just before mounting
the root filesystem) rather than at first reference. Note that
forceload expects a filename which includes the directory. Also
note that loading a module does not necessarily imply that it will
be installed.
*& & & & & & &
forceload: drv/foo
Set an integer variable in the kernel or a module to a new value.
This facility should be used with caution.& See system(4).
To set variables in 'unix':
*& & & & & & &
set nautopush=32
*& & & & & & &
set maxusers=40
To set a variable named 'debug' in the module named 'test_module'
*& & & & & & &
set test_module:debug = 0x13
* Begin FJSVssf (do not edit)
set ftrace_atboot = 1
set kmem_flags = 0x100
set kmem_lite_maxalign = 8192
* End FJSVssf (do not edit)
forceload: drv/fjpfca
* Begin MDD root info (do not edit)
rootdev:/pseudo/md@0:0,0,blk
* End MDD root info (do not edit)
*** Begin EMCpower added lines *** DO NOT EDIT BELOW THIS LINE ***
forceload: drv/emcpsf
forceload: drv/sd
forceload: drv/ssd
forceload: drv/emcp
forceload: misc/emcpgpx
forceload: misc/emcpmpx
forceload: misc/emcpvlumd
forceload: misc/emcpxcrypt
forceload: misc/emcpdm
forceload: misc/emcpioc
set emcp:bPxEnableInit=1
*** End EMCpower added lines *** DO NOT EDIT ABOVE THIS LINE ***
exec_user_stack = 1& & & & & & & & & & & & & &
set noexec_user_stack_log = 1
查看/etc/project如下：
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
user.oracle:100::oracle::
综上所述，从listener.log的输出看，nofiles的soft limit过小，只有256，这会导致Solaris Error: 24: Too many open files 错误。从/etc/system和/etc/project，我们没有看到设置了soft limit。
故障处理小结及后续建议
将oracle用户的soft limit提升为至少1024，然后重新oracle用户登录，检验ulimit合格后，重新启动数据库和监听。
&具体解决办法如下：
1、在/etc/system增加以下行
set rlim_fd_max=65536
set rlim_fd_cur=4096
2、重新登录ORACLE并检验oracle用户的限制
su & oracle
ulimit -Ha
ulimit &Sa
3、重新启动数据库和监听
更多Oracle相关信息见 专题页面
本文永久更新链接地址：
相关资讯 & & &
& (04月23日)
& (08/21/:58)
& (11/26/:04)
& (03月28日)
& (02/12/:40)
& (09/14/:00)
同意评论声明
发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款感悟代码魅力，享受美好人生！
涉及到表的处理请参看原表结构与数据&&
使用select into语句读取tb_Employee的一行，使用异常处理处理no_data_found和two_many_rows的系统预定义异常
set serveroutput on;
emp tb_Employee%
select * into emp from tb_Employee
where ename = 'SCOTT';
dbms_output.put_line('SCOTT ''s sal is : ' || emp.sal);
when no_data_found then
dbms_output.put_line('no data found exception');
emp tb_Employee%
select * into emp from tb_Employee
where deptno =20;
dbms_output.put_line('The sal is : ' || emp.sal);
when too_many_rows then
dbms_output.put_line('too many rows exception');
使用嵌套异常端处理，循环读取emp_id_minval 和 emp_id_maxval之间的员工，使得其中存在不存在员工号。输出对应员工的ename,不存在的员工输出¬ exists such empolyee&。
emp_id tb_Employee.pk_Employee_ID%
v_ename tb_Employee.ename%
emp_id_minval tb_Employee.pk_Employee_ID%
emp_id_maxval tb_Employee.pk_Employee_ID%
emp_id_minval:=&emp_id_
emp_id_maxval:=&emp_id_
for emp_id in emp_id_minval..emp_id_maxval loop
select ename into v_ename from tb_Employee
where pk_Employee_ID=emp_
dbms_output.put_line('ename:'||v_ename);
when no_data_found then
dbms_output.put_line('not exists such empolyee');
写一个处理ora-01400 （不能插入空值）的系统异常的示例程序和异常处理
pragma exception_init(insert_excep, -01400);
insert into tb_Department
(pk_Department_ID, dname) values (50, null);
when insert_excep then
dbms_output.put_line('INSERT OPERATION FAILED');
dbms_output.put_line(SQLERRM);
使用SQLCODE,SQLERRM,获得异常的编号和错误信息，并输出异常的编号和错误信息
emp tb_Employee %
error_code number;
error_message varchar2(255);
select * into emp from tb_Employee
where ename = 'SCOTT';
dbms_output.put_line('SCOTT ''s salary is : ' || emp.sal);
when too_many_rows then
error_code := SQLCODE;
error_message := SQLERRM;
dbms_output.put_line(error_code || ' ' || error_message);
自定义一个异常，抛出这个异常并处理
name varchar2(20) := '&name';
deptno number := &
update tb_Department
set dname = name
where pk_Department_ID =
if sql % notfound then
raise invalid_
when invalid_department then
dbms_output.put_line('No such department');
使用raise_application_error抛出一个应用异常
v_ename tb_Employee.ename%type:='&v_ename';
pragma exception_init(e_name, -20999);
delete from tb_Employee
where ename = v_
if sql % notfound then
raise_application_error(-20999, 'This is not a valid ename');
when e_name then
dbms_output.put_line('The ename '||v_ename||' exists, Please choose again');
阅读(...) 评论()Oracle 顺序控制,异常处理,动态sql - 百行孝为先，万恶懒为首
- ITeye技术网站
博客分类：
-----------------顺序控制--------------------
GOTO语句 ：用于跳转到特定标记号去执行语句
(使可读性变差)
--2. null 语句 ：不会执行任何操作，并直接将控制传递下一条语句
（使可读性变强）
v_sal emp.sal%
v_name emp.ename%type；
select sal,ename into v_sal,v_ename from emp where empno=&
if v_sal&3000 then
update emp set comm=sal*0.1 where ename=v_
---------------------异常处理--------------------
异常错误的名称1 [or 异常错误名称2...] then
执行语句1；
异常错误的名称3 [or 异常错误名称4...] then
执行语句2；
when others then
执行语句3；
v_dept_row dept%
--定义变量，用于保存dept表的相关信息
select * into v_dept_
--查询dept中的所有记录 将触发too_mang_rows 异常;
--向dept表总插入数据， 会违反主键唯一约束， 会触发 DUP_val_on_index 异常
insert into dept values(10,'product','china');
when too_many_rows then
Dbms_Output.put_line('返回了多行，请使用游标来处理多行记录的集合！');
when DUP_val_on_index then
Dbms_Output.put.line('主键不能重复！');
-----------------自定义异常--------------------------
ept_no_emp E
update emp set comm=case when comm is null then 50 else comm*1.1 end where empno=&
If sql%notfound then
raise ept_no_
--如果没有数据更新 就抛出异常 使用
DBMS_output.put_line('奖金已经更新了！');
when ept_no_emp then
Dbms_Output.put_line('该员工编号不存在！');
--开发动态SQL
type Create_table_record is record
filed_name varchar2(15),
filed_type varchar2(15),
filed_explain varchar2(15)
type Dynamic_sql_table is table of Create_table_record index by binary_
v_dynamic_sql_table_name Dynamic_sql_
v_create_table_name varchar2(20);
v_dynamic_ddl_sal varchar2(500);
v_dynamic_dcl_sal varchar2(500);
v_grant_user varchar2(10);
v_grant_authority varchar2(10);
v_create_table_name:='STUD';
v_grant_user:='scott';
v_grant_authority:='select';
v_dynamic_sql_table(0).field_name:='sid';
v_dynamic_sql_table(0).field_type:='varcar2(10)';
v_dynamic_sql_table(0).field_explain:='primary key';
v_dynamic_sql_table(1).field_name:='sname';
v_dynamic_sql_table(1).field_type:='varcar2(10)';
v_dynamic_sql_table(1).field_explain:='not null';
v_dynamic_sql_table(2).field_name:='sclass';
v_dynamic_sql_table(2).field_type:='varcar2(10)';
v_dynamic_sql_table(2).field_explain:='not null';
v_dynamic_ddl_sql:='create table'||v_create_table_name||chr(13)||'('||chr(13);
for i in 1..v_dynamic_sql_table.count-1
v_dynamic_ddl_sql：=v_dynamic_ddl_sql||v_dynamic_sql_table(i).field_name ||' '||
v_dynamic_sql_table(i).field_type || ' '||v_dynamic_sql_table(i).field_explain ||','||chr(13);
v_dynamic_ddl_sql:=substr(v_dynamic_ddl_sql,0,lenth(v_dyname_ddl_sql)-2);
v_dynamic_ddl_sql:=v_dynamic_ddl_sql||chr(13)||')';
v_dynamic_dcl_sql:='grant'||v_grant_authority||'on'||c_create_table_name||'to'||v_grant_
execute immediate v_dynamic_ddl_
execute immediate v_dynamic_dcl_
--处理无占位符和returning子句的DML语句
v_dynamic_sql varchar2(100);
v_dynamic_sql:='Update emp set sal=sal*1.1 where deptno=30';
execute immediate v_dynamic_
v_dynamic_sql varchar2(100);
v_dynamic_sql:='update emp set sal=sal*(1+:percent/100.0) where deptno=:deptno';
execute immediate v_dynamic_sql Using &percent,&
v_dynamic_sql varchar2(100);
--存储SQL语句的变量
v_sal number(7,2);
--存储工资的变量
v_empno number(4):=&
--接受用户输入的编号变量
v_percent number(2):=&
--定义增加员工工资的百分比变量
select sal into v_sal from emp where empno=v_ --查询更新前的个工资
DBMS_OUTPUT.put_line('更新前的工资'||v_sal);
--要执行的sql 语句 并返回 跟新后的 sal
v_dynamic_sql:='update emp set sal=sal*(1+:percent/100.0)' where empno=:empno returning sal into :
--执行定义的sql语句
并使用returning into 接受放回的值；
execute immediate v_dyname_sql using v_percent,v_empno returning into v_
Dbms_Output.put_line('增长率：'||v_percent||'%');
Dbms_Output.put_line('新工资：'||v_sal);
--处理单行查询语句
v_dynamic_sql varchar2(100);
emp_record emp%
v_dynamic_sql:='select * from emp where empno=:empno';
execute immediate v_dynamic_sql into emp_record using &
DBMS_output.put_line('雇员：'||emp_record.ename||'的工资是:'||emp_record_sal);
是用Bulk collect
type emp_ename_table Is table of emp.ename%type index by binary_
--一个列类型
v_emp_ename_table emp_ename_
--一个列类型的变量
v_dynamic_sql varchar2(100);
--保存要执行的sql语句
v_dynamic_sql:='select ename from emp where deptno=:deptno';
--给变量赋值
execute immediate v_dynamic_sql bulk collect into v_emp_enam_table using &
for i in 1..v_emp_ename_table.cont
dbms_output.put_line(v_emp_ename_table(i));
浏览: 861356 次
来自: 上海
这个挺好JavaScript实现input输入框控件只允许输入 ...
东西太好啦受教啊
naiyizute 写道编码表好像不全，能提供全一点的吗htt ...
你这个 EmojiConverter 是来自这里吧，一个日本人 ...
写的真好，根据这篇搭建文章，我自己整理了一下，完整的包和可以在 ...insert into select批量加载出错解决方案
insert into select批量加载出错解决方案
当使用insert into select 批量加载数据的时候，可能会碰到因为某些数据不符合加载条件，而导致整个insert 语句无法执行，全部rollback。这时可以使用DML 错误日志的特性，解决这个问题。 & &
只需要创建一个日志表，并且在使用dml语句的时候添加dml error logging 语句，即可将错误的rows记录到日志表中，而且不会影响已经加载到表中的数据。最后修正这些无法加载的数据。
操作步骤如下：
1. & & 创建一个日志表
可以使用DBMS_ERRLOG包创建，或者手动的创建日志表。如果使用dbms_errlog.create_error_log来创建日志表，默认的会将源表的所有列都加入需要记录的行列中。
2. & & 执行insert ，并且添加error logging 语句。
3. & & 最后查询日志表，修改无法加载的数据。
以下是一个insert into select 批量加载的例子：
第一步创建日志表
创建测试表target_t
create table target_t (id number(4),namevarchar2(2000)) ; &
创建源表source_t
create table source_t as select level as id ,'name' || level as name from dual connect by level &= 10004 ; &
使用存储过程创建error logging table target_err_t
使用DBMS_ERRLOG.CREATE_ERROR_LOG会自动补全表中的所有列到日志表中。（这里表名竟然不区分大小写，看来Dabase在向好的方向发展了）
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name =& 'target_t',err_log_table_name=& 'target_err_log') ; &
查看一下表结构
dexter@ORCL& desc target_err_ &
&Name & & & & & & & & & & & & & & & & & & & & & & & & Null? & &Type &
&------------------------------------------------------------- ------------------------------------ &
&ORA_ERR_NUMBER$ & & & & & & & & & & & & & & & & & & & & & & & NUMBER &
&ORA_ERR_MESG$ & & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(2000) &
&ORA_ERR_ROWID$ & & & & & & & & & & & & & & & & & & & & & & & &ROWID &
&ORA_ERR_OPTYP$ & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(2) &
&ORA_ERR_TAG$ & & & & & & & & & & & & & & & & & & & & & & & & & VARCHAR2(2000) &
&ID & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(4000) &
&NAME & & & & & & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(4000) &
第二步执行insert
这里多了error logging 语句LOG ERRORS INTO target_err_log ('daily_load')REJECT LIMIT 4，其中target_err_log为error日志表名，'daily_load'对应日志表中的ORA_ERR_TAG$，便于区分每次的操作。reject limit 4 中的4表示最多可以遇到4次错误。但是error 日志表中最多会插入5条记录（因为只有碰到第五次错误才会终止嘛）。另外，insert 操作 与 日志表操作不在同一个事务，也就是说当前insert 操作的 commit 和 rollback 不会影响日志表中的记录。
insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load') REJECT LIMIT 4 ; &
& & & & & & & & & & & & & & * &
ERROR at line 1: &
ORA-01438: value larger than specifiedprecision allowed for this column &
col ORA_ERR_NUMBER$ for 999999 &
col ORA_ERR_MESG$ for a40 &
col ORA_ERR_ROWID$ for a10 &
col ORA_ERR_OPTYP$ for a10 &
col ORA_ERR_TAG$ for a20 &
col id for a20 &
col name for a20 &
set lines 150 &
dexter@ORCL& select * from target_err_ &
ORA_ERR_NUMBER$ ORA_ERR_MESG$ & & & & & & & & & & & & & &ORA_ERR_ROORA_ERR_OP ORA_ERR_TAG$ & & & & ID & & & & & & & & & NAME &
------------------------------------------------------- ---------- ------------------------------ -------------------- -------------------- &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10000 & & & &name10000 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10001 & & & &name10001 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10002 & & & &name10002 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10003 & & & &name10003 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10004 & & & &name10004 &
& & & & & & & &recision allowed for this column &
一共5条无法加载的数据，完整记录了下来
insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5; &
9999 rows created. &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & &9999 &
支持rollback &
dexter@ORCL& &
Rollback complete. &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & & & 0 &
dexter@ORCL& insert into target_t select *from source_t LOG ERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5; &
9999 rows created. &
dexter@ORCL& &
Commit complete. &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & &9999 &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & & & &15 &
第三步修改数据
这里我就不多说。
dml error logging 还支持UPDATE, MERGE,DELETE语句。
日志表中各字段解读
Column Name & & & & & & & & & & & & & & & & & &Data Type & & & & & & & & & & & & & & & & & &Description
ORA_ERR_NUMBER$ & & & & & & & & & & & NUMBER & & & & & & & & & & & & & & & & & &Oracle error number
ORA_ERR_MESG$ & & & & & & & & & & & & & &VARCHAR2(2000) & & & & & & & & & & & &Oracle errormessage text
ORA_ERR_ROWID$ & & & & & & & & & & & & & ROWID & & & & & & & & & & & & & & & & & & & &Rowid of the row inerror (for update and delete)
ORA_ERR_OPTYP$ & & & & & & & & & & & & & VARCHAR2(2) & & & & & & & & & & & & & & &Type ofoperation: insert (I), update (U), delete (D)
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &Note: Errors from the update clause and
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &insert clause of a MERGEoperation are
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &distinguished by the Uand Ivalues.
ORA_ERR_TAG$ & & & & & & & & & & & & & & & & VARCHAR2(2000) & & & & & & & & & & & & Value of thetag supplied by the user in the error logging clause
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'}
你好，游客
Oracle监听不定期出现异常故障处理
来源：Linux社区&
作者：passion_of_data
环境：操作系统为：SunOS 5.10数据库版本： RAC 11.2.0.3.0该主机上有2个库，一套RAC的节点1在上面，还有另外一个库，之所以使用的是DB下的监听而没用GRID下的监听，是为了避免停掉CRS时影响另外一个库的使用。
故障现象：数据库监听不定期出现异常 ,从应用tnsping数据库，时间花费很长甚至连不上
故障分析处理过程：
从应用主机tnsping如下：
racdb1_scenemon$tnsping racdb_new&TNS Ping Utility for Solaris: Version 11.2.0.3.0 - Production on 19-2月 -:23&Copyright (c) , Oracle. All rights reserved.&已使用的参数文件:
已使用 TNSNAMES 适配器来解析别名&尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 11.111.11.2)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))&OK (60000 毫秒)
Oracle监听之动态监听与静态监听特点
Oracle 11g RAC 环境下单实例非缺省监听及端口配置
Oracle 监听器日志配置与管理
Oracle错误- ORA-12514：TNS：无监听程序
ORA-12514 监听错误解决
Oracle监听器出现的6种连接问题及其解决方法
Oracle LISTENER 未监听到Oracle实例问题解决
从数据库服务器上查看监听状态：
$ lsnrctl status&LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 19-FEB-:58&Copyright (c) , Oracle. All rights reserved.&Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))&
TNS-12535: TNS:operation timed out&TNS-12560: TNS:protocol adapter error&TNS-00505: Operation timed out& &
将监听进程予以kill并重启监听：
$ ps -ef |grep tns&oracle
0 15:45:15 pts/10 0:00 grep tns&oracle
Nov 28 ? 6:08 /oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER -inherit&oracle
Jan 24 ? 4447:11 /oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER -inherit&$ &$ kill -9 9488&$ lsnrctl start&LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 19-FEB-:22&Copyright (c) , Oracle. All rights reserved.&Starting /oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...&TNSLSNR for Solaris: Version 11.2.0.3.0 - Production&Log messages written to /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml&Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))&Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))&STATUS of the LISTENER&------------------------&Alias LISTENER&Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production&Start Date 19-FEB-:22&Uptime 0 days 0 hr. 0 min. 0 sec&Trace Level off&Security ON: Local OS Authentication&SNMP OFF&Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/racdb/listener/alert/log.xml&Listening Endpoints Summary...&(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racdb)(PORT=1521)))&The listener supports no services&The command completed successfully& &
检查listener.log如下：
17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41603)) * establish * racdb * 0&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41659)) * establish * racdb * 12518&TNS-12518: TNS:listener could not hand off client connection&TNS-12560: TNS:protocol adapter error&TNS-00530: Protocol adapter error&Solaris Error: 24: Too many open files &&&&&&&&&&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41652)) * establish * racdb * 0&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41669)) * establish * racdb * 0&17-FEB-:26 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))(CID=(PROGRAM=sqlplus@racdb1)(HOST=racdb1)(USER=tmn))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.111.11.89)(PORT=41682)) * establish * racdb * 12518&TNS-12518: TNS:listener could not hand off client connection&TNS-12560: TNS:protocol adapter error&TNS-00530: Protocol adapter error&Solaris Error: 24: Too many open files &&&&&&&&&&
Too many open files意味着Maximum Number Of Open Files Per Process 达到了上限。因此listener hang住的原因可能是该limit设置过小，进一步查看硬件限制如下：
$ ulimit -Ha&time(seconds) unlimited&file(blocks) unlimited&data(kbytes) unlimited&stack(kbytes) unlimited&coredump(blocks) unlimited&nofiles(descriptors) 65536&memory(kbytes) unlimited
查看软件限制如下：
$ ulimit -Sa&time(seconds) unlimited&file(blocks) unlimited&data(kbytes) unlimited&stack(kbytes) 8192&coredump(blocks) unlimited&nofiles(descriptors) 256&memory(kbytes) unlimited
查看/etc/system如下：
*ident& "@(#)system& &
1.18& & 97/06/27 SMI" /* SVR4 1.5 */
* SYSTEM SPECIFICATION FILE
Set the search path for modules.& This has a format similar to the
csh path variable. If the module isn't found in the first directory
it tries the second and so on. The default is /kernel /usr/kernel
*& & & & & & &
moddir: /kernel /usr/kernel /other/modules
* root device and root filesystem configuration:
The following may be used to override the defaults provided by
the boot program:
rootfs:& & & &
Set the filesystem type of the root.
rootdev:& & & & Set the root device.& This should be a fully
*& & & & & & & & & & &
expanded physical pathname.& The default is the
*& & & & & & & & & & &
physical pathname of the device where the boot
*& & & & & & & & & & &
program resides.& The physical pathname is
*& & & & & & & & & & &
highly platform and configuration dependent.
*& & & & & & &
rootfs:ufs
*& & & & & & &
rootdev:/sbus@1,f8000000/esp@0,800000/sd@3,0:a
(Swap device configuration should be specified in /etc/vfstab.)
* exclude:
Modules appearing in the moddir path which are NOT to be loaded,
even if referenced. Note that `exclude' accepts either a module name,
or a filename which includes the directory.
*& & & & & & &
exclude: win
*& & & & & & &
exclude: sys/shmsys
* forceload:
Cause these modules to be loaded at boot time, (just before mounting
the root filesystem) rather than at first reference. Note that
forceload expects a filename which includes the directory. Also
note that loading a module does not necessarily imply that it will
be installed.
*& & & & & & &
forceload: drv/foo
Set an integer variable in the kernel or a module to a new value.
This facility should be used with caution.& See system(4).
To set variables in 'unix':
*& & & & & & &
set nautopush=32
*& & & & & & &
set maxusers=40
To set a variable named 'debug' in the module named 'test_module'
*& & & & & & &
set test_module:debug = 0x13
* Begin FJSVssf (do not edit)
set ftrace_atboot = 1
set kmem_flags = 0x100
set kmem_lite_maxalign = 8192
* End FJSVssf (do not edit)
forceload: drv/fjpfca
* Begin MDD root info (do not edit)
rootdev:/pseudo/md@0:0,0,blk
* End MDD root info (do not edit)
*** Begin EMCpower added lines *** DO NOT EDIT BELOW THIS LINE ***
forceload: drv/emcpsf
forceload: drv/sd
forceload: drv/ssd
forceload: drv/emcp
forceload: misc/emcpgpx
forceload: misc/emcpmpx
forceload: misc/emcpvlumd
forceload: misc/emcpxcrypt
forceload: misc/emcpdm
forceload: misc/emcpioc
set emcp:bPxEnableInit=1
*** End EMCpower added lines *** DO NOT EDIT ABOVE THIS LINE ***
exec_user_stack = 1& & & & & & & & & & & & & &
set noexec_user_stack_log = 1
查看/etc/project如下：
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
user.oracle:100::oracle::
综上所述，从listener.log的输出看，nofiles的soft limit过小，只有256，这会导致Solaris Error: 24: Too many open files 错误。从/etc/system和/etc/project，我们没有看到设置了soft limit。
故障处理小结及后续建议
将oracle用户的soft limit提升为至少1024，然后重新oracle用户登录，检验ulimit合格后，重新启动数据库和监听。
&具体解决办法如下：
1、在/etc/system增加以下行
set rlim_fd_max=65536
set rlim_fd_cur=4096
2、重新登录ORACLE并检验oracle用户的限制
su & oracle
ulimit -Ha
ulimit &Sa
3、重新启动数据库和监听
更多Oracle相关信息见 专题页面
本文永久更新链接地址：
相关资讯 & & &
& (04月23日)
& (08/21/:58)
& (11/26/:04)
& (03月28日)
& (02/12/:40)
& (09/14/:00)
同意评论声明
发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款感悟代码魅力，享受美好人生！
涉及到表的处理请参看原表结构与数据&&
使用select into语句读取tb_Employee的一行，使用异常处理处理no_data_found和two_many_rows的系统预定义异常
set serveroutput on;
emp tb_Employee%
select * into emp from tb_Employee
where ename = 'SCOTT';
dbms_output.put_line('SCOTT ''s sal is : ' || emp.sal);
when no_data_found then
dbms_output.put_line('no data found exception');
emp tb_Employee%
select * into emp from tb_Employee
where deptno =20;
dbms_output.put_line('The sal is : ' || emp.sal);
when too_many_rows then
dbms_output.put_line('too many rows exception');
使用嵌套异常端处理，循环读取emp_id_minval 和 emp_id_maxval之间的员工，使得其中存在不存在员工号。输出对应员工的ename,不存在的员工输出¬ exists such empolyee&。
emp_id tb_Employee.pk_Employee_ID%
v_ename tb_Employee.ename%
emp_id_minval tb_Employee.pk_Employee_ID%
emp_id_maxval tb_Employee.pk_Employee_ID%
emp_id_minval:=&emp_id_
emp_id_maxval:=&emp_id_
for emp_id in emp_id_minval..emp_id_maxval loop
select ename into v_ename from tb_Employee
where pk_Employee_ID=emp_
dbms_output.put_line('ename:'||v_ename);
when no_data_found then
dbms_output.put_line('not exists such empolyee');
写一个处理ora-01400 （不能插入空值）的系统异常的示例程序和异常处理
pragma exception_init(insert_excep, -01400);
insert into tb_Department
(pk_Department_ID, dname) values (50, null);
when insert_excep then
dbms_output.put_line('INSERT OPERATION FAILED');
dbms_output.put_line(SQLERRM);
使用SQLCODE,SQLERRM,获得异常的编号和错误信息，并输出异常的编号和错误信息
emp tb_Employee %
error_code number;
error_message varchar2(255);
select * into emp from tb_Employee
where ename = 'SCOTT';
dbms_output.put_line('SCOTT ''s salary is : ' || emp.sal);
when too_many_rows then
error_code := SQLCODE;
error_message := SQLERRM;
dbms_output.put_line(error_code || ' ' || error_message);
自定义一个异常，抛出这个异常并处理
name varchar2(20) := '&name';
deptno number := &
update tb_Department
set dname = name
where pk_Department_ID =
if sql % notfound then
raise invalid_
when invalid_department then
dbms_output.put_line('No such department');
使用raise_application_error抛出一个应用异常
v_ename tb_Employee.ename%type:='&v_ename';
pragma exception_init(e_name, -20999);
delete from tb_Employee
where ename = v_
if sql % notfound then
raise_application_error(-20999, 'This is not a valid ename');
when e_name then
dbms_output.put_line('The ename '||v_ename||' exists, Please choose again');
阅读(...) 评论()Oracle 顺序控制,异常处理,动态sql - 百行孝为先，万恶懒为首
- ITeye技术网站
博客分类：
-----------------顺序控制--------------------
GOTO语句 ：用于跳转到特定标记号去执行语句
(使可读性变差)
--2. null 语句 ：不会执行任何操作，并直接将控制传递下一条语句
（使可读性变强）
v_sal emp.sal%
v_name emp.ename%type；
select sal,ename into v_sal,v_ename from emp where empno=&
if v_sal&3000 then
update emp set comm=sal*0.1 where ename=v_
---------------------异常处理--------------------
异常错误的名称1 [or 异常错误名称2...] then
执行语句1；
异常错误的名称3 [or 异常错误名称4...] then
执行语句2；
when others then
执行语句3；
v_dept_row dept%
--定义变量，用于保存dept表的相关信息
select * into v_dept_
--查询dept中的所有记录 将触发too_mang_rows 异常;
--向dept表总插入数据， 会违反主键唯一约束， 会触发 DUP_val_on_index 异常
insert into dept values(10,'product','china');
when too_many_rows then
Dbms_Output.put_line('返回了多行，请使用游标来处理多行记录的集合！');
when DUP_val_on_index then
Dbms_Output.put.line('主键不能重复！');
-----------------自定义异常--------------------------
ept_no_emp E
update emp set comm=case when comm is null then 50 else comm*1.1 end where empno=&
If sql%notfound then
raise ept_no_
--如果没有数据更新 就抛出异常 使用
DBMS_output.put_line('奖金已经更新了！');
when ept_no_emp then
Dbms_Output.put_line('该员工编号不存在！');
--开发动态SQL
type Create_table_record is record
filed_name varchar2(15),
filed_type varchar2(15),
filed_explain varchar2(15)
type Dynamic_sql_table is table of Create_table_record index by binary_
v_dynamic_sql_table_name Dynamic_sql_
v_create_table_name varchar2(20);
v_dynamic_ddl_sal varchar2(500);
v_dynamic_dcl_sal varchar2(500);
v_grant_user varchar2(10);
v_grant_authority varchar2(10);
v_create_table_name:='STUD';
v_grant_user:='scott';
v_grant_authority:='select';
v_dynamic_sql_table(0).field_name:='sid';
v_dynamic_sql_table(0).field_type:='varcar2(10)';
v_dynamic_sql_table(0).field_explain:='primary key';
v_dynamic_sql_table(1).field_name:='sname';
v_dynamic_sql_table(1).field_type:='varcar2(10)';
v_dynamic_sql_table(1).field_explain:='not null';
v_dynamic_sql_table(2).field_name:='sclass';
v_dynamic_sql_table(2).field_type:='varcar2(10)';
v_dynamic_sql_table(2).field_explain:='not null';
v_dynamic_ddl_sql:='create table'||v_create_table_name||chr(13)||'('||chr(13);
for i in 1..v_dynamic_sql_table.count-1
v_dynamic_ddl_sql：=v_dynamic_ddl_sql||v_dynamic_sql_table(i).field_name ||' '||
v_dynamic_sql_table(i).field_type || ' '||v_dynamic_sql_table(i).field_explain ||','||chr(13);
v_dynamic_ddl_sql:=substr(v_dynamic_ddl_sql,0,lenth(v_dyname_ddl_sql)-2);
v_dynamic_ddl_sql:=v_dynamic_ddl_sql||chr(13)||')';
v_dynamic_dcl_sql:='grant'||v_grant_authority||'on'||c_create_table_name||'to'||v_grant_
execute immediate v_dynamic_ddl_
execute immediate v_dynamic_dcl_
--处理无占位符和returning子句的DML语句
v_dynamic_sql varchar2(100);
v_dynamic_sql:='Update emp set sal=sal*1.1 where deptno=30';
execute immediate v_dynamic_
v_dynamic_sql varchar2(100);
v_dynamic_sql:='update emp set sal=sal*(1+:percent/100.0) where deptno=:deptno';
execute immediate v_dynamic_sql Using &percent,&
v_dynamic_sql varchar2(100);
--存储SQL语句的变量
v_sal number(7,2);
--存储工资的变量
v_empno number(4):=&
--接受用户输入的编号变量
v_percent number(2):=&
--定义增加员工工资的百分比变量
select sal into v_sal from emp where empno=v_ --查询更新前的个工资
DBMS_OUTPUT.put_line('更新前的工资'||v_sal);
--要执行的sql 语句 并返回 跟新后的 sal
v_dynamic_sql:='update emp set sal=sal*(1+:percent/100.0)' where empno=:empno returning sal into :
--执行定义的sql语句
并使用returning into 接受放回的值；
execute immediate v_dyname_sql using v_percent,v_empno returning into v_
Dbms_Output.put_line('增长率：'||v_percent||'%');
Dbms_Output.put_line('新工资：'||v_sal);
--处理单行查询语句
v_dynamic_sql varchar2(100);
emp_record emp%
v_dynamic_sql:='select * from emp where empno=:empno';
execute immediate v_dynamic_sql into emp_record using &
DBMS_output.put_line('雇员：'||emp_record.ename||'的工资是:'||emp_record_sal);
是用Bulk collect
type emp_ename_table Is table of emp.ename%type index by binary_
--一个列类型
v_emp_ename_table emp_ename_
--一个列类型的变量
v_dynamic_sql varchar2(100);
--保存要执行的sql语句
v_dynamic_sql:='select ename from emp where deptno=:deptno';
--给变量赋值
execute immediate v_dynamic_sql bulk collect into v_emp_enam_table using &
for i in 1..v_emp_ename_table.cont
dbms_output.put_line(v_emp_ename_table(i));
浏览: 861356 次
来自: 上海
这个挺好JavaScript实现input输入框控件只允许输入 ...
东西太好啦受教啊
naiyizute 写道编码表好像不全，能提供全一点的吗htt ...
你这个 EmojiConverter 是来自这里吧，一个日本人 ...
写的真好，根据这篇搭建文章，我自己整理了一下，完整的包和可以在 ...insert into select批量加载出错解决方案
insert into select批量加载出错解决方案
当使用insert into select 批量加载数据的时候，可能会碰到因为某些数据不符合加载条件，而导致整个insert 语句无法执行，全部rollback。这时可以使用DML 错误日志的特性，解决这个问题。 & &
只需要创建一个日志表，并且在使用dml语句的时候添加dml error logging 语句，即可将错误的rows记录到日志表中，而且不会影响已经加载到表中的数据。最后修正这些无法加载的数据。
操作步骤如下：
1. & & 创建一个日志表
可以使用DBMS_ERRLOG包创建，或者手动的创建日志表。如果使用dbms_errlog.create_error_log来创建日志表，默认的会将源表的所有列都加入需要记录的行列中。
2. & & 执行insert ，并且添加error logging 语句。
3. & & 最后查询日志表，修改无法加载的数据。
以下是一个insert into select 批量加载的例子：
第一步创建日志表
创建测试表target_t
create table target_t (id number(4),namevarchar2(2000)) ; &
创建源表source_t
create table source_t as select level as id ,'name' || level as name from dual connect by level &= 10004 ; &
使用存储过程创建error logging table target_err_t
使用DBMS_ERRLOG.CREATE_ERROR_LOG会自动补全表中的所有列到日志表中。（这里表名竟然不区分大小写，看来Dabase在向好的方向发展了）
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name =& 'target_t',err_log_table_name=& 'target_err_log') ; &
查看一下表结构
dexter@ORCL& desc target_err_ &
&Name & & & & & & & & & & & & & & & & & & & & & & & & Null? & &Type &
&------------------------------------------------------------- ------------------------------------ &
&ORA_ERR_NUMBER$ & & & & & & & & & & & & & & & & & & & & & & & NUMBER &
&ORA_ERR_MESG$ & & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(2000) &
&ORA_ERR_ROWID$ & & & & & & & & & & & & & & & & & & & & & & & &ROWID &
&ORA_ERR_OPTYP$ & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(2) &
&ORA_ERR_TAG$ & & & & & & & & & & & & & & & & & & & & & & & & & VARCHAR2(2000) &
&ID & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(4000) &
&NAME & & & & & & & & & & & & & & & & & & & & & & & & & & & & &VARCHAR2(4000) &
第二步执行insert
这里多了error logging 语句LOG ERRORS INTO target_err_log ('daily_load')REJECT LIMIT 4，其中target_err_log为error日志表名，'daily_load'对应日志表中的ORA_ERR_TAG$，便于区分每次的操作。reject limit 4 中的4表示最多可以遇到4次错误。但是error 日志表中最多会插入5条记录（因为只有碰到第五次错误才会终止嘛）。另外，insert 操作 与 日志表操作不在同一个事务，也就是说当前insert 操作的 commit 和 rollback 不会影响日志表中的记录。
insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load') REJECT LIMIT 4 ; &
& & & & & & & & & & & & & & * &
ERROR at line 1: &
ORA-01438: value larger than specifiedprecision allowed for this column &
col ORA_ERR_NUMBER$ for 999999 &
col ORA_ERR_MESG$ for a40 &
col ORA_ERR_ROWID$ for a10 &
col ORA_ERR_OPTYP$ for a10 &
col ORA_ERR_TAG$ for a20 &
col id for a20 &
col name for a20 &
set lines 150 &
dexter@ORCL& select * from target_err_ &
ORA_ERR_NUMBER$ ORA_ERR_MESG$ & & & & & & & & & & & & & &ORA_ERR_ROORA_ERR_OP ORA_ERR_TAG$ & & & & ID & & & & & & & & & NAME &
------------------------------------------------------- ---------- ------------------------------ -------------------- -------------------- &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10000 & & & &name10000 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10001 & & & &name10001 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10002 & & & &name10002 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10003 & & & &name10003 &
& & & & & & & &recision allowed for this column &
& & & & & 1438 ORA-01438: value larger than specified p & & & & & &I & & & & &daily_load & & & & & 10004 & & & &name10004 &
& & & & & & & &recision allowed for this column &
一共5条无法加载的数据，完整记录了下来
insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5; &
9999 rows created. &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & &9999 &
支持rollback &
dexter@ORCL& &
Rollback complete. &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & & & 0 &
dexter@ORCL& insert into target_t select *from source_t LOG ERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5; &
9999 rows created. &
dexter@ORCL& &
Commit complete. &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & &9999 &
dexter@ORCL& select count(*) from target_ &
&COUNT(*) &
---------- &
& & & & &15 &
第三步修改数据
这里我就不多说。
dml error logging 还支持UPDATE, MERGE,DELETE语句。
日志表中各字段解读
Column Name & & & & & & & & & & & & & & & & & &Data Type & & & & & & & & & & & & & & & & & &Description
ORA_ERR_NUMBER$ & & & & & & & & & & & NUMBER & & & & & & & & & & & & & & & & & &Oracle error number
ORA_ERR_MESG$ & & & & & & & & & & & & & &VARCHAR2(2000) & & & & & & & & & & & &Oracle errormessage text
ORA_ERR_ROWID$ & & & & & & & & & & & & & ROWID & & & & & & & & & & & & & & & & & & & &Rowid of the row inerror (for update and delete)
ORA_ERR_OPTYP$ & & & & & & & & & & & & & VARCHAR2(2) & & & & & & & & & & & & & & &Type ofoperation: insert (I), update (U), delete (D)
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &Note: Errors from the update clause and
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &insert clause of a MERGEoperation are
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &distinguished by the Uand Ivalues.
ORA_ERR_TAG$ & & & & & & & & & & & & & & & & VARCHAR2(2000) & & & & & & & & & & & & Value of thetag supplied by the user in the error logging clause
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'}
我要回帖
更多关于 oracle 函数异常处理 的文章
更多推荐
- ·怎么不删怎样单独删除朋友圈的一张照片替换照片和赞
- ·微信朋友圈只有共同好友才能看见赞吗怎么不删赞又能显示照片。
- ·沐曦股票退市了手里的股票怎么办在那上市的？
- ·如何怎样开淘宝店铺流程开店运营？
- ·如何成为一名b站的舰长有什么用？
- ·半导体光电流不响怎么修?电池按上去没有电流
- ·求好人分享百度云资源
- ·人脑与电脑花朵有什么共同点点
- ·qq删了好友怎么恢复手机版qq聊天记录恢复
- ·微信小程序 电商对微商有何影响 能推动电商发展吗
- ·手机上怎么删除定时说说怎么删除？？？急急急～
- ·电脑wd8win10系统文件夹加密咋加密
- ·邮件不能连接到qq账户为什么连接不了
- ·是不是有地方可以看各种vip电视软件vip的软件？
- ·现在旅游app去旅游不满二不满十四周岁的人犯罪不能组团吗
- ·请问这是手机总死机怎么回事事？直接死机
- ·微信内置浏览器打开qq如何打开app客户端
- ·有不接电源语音唤醒siri的软件没有？求求大神帮帮忙。
- ·电信网络机顶盒看电视软件没网络时能看电视吗
- ·i3的i36100集成显卡怎么样很差吗
- ·oracle 游标异常处理的select怎么处理异常
- ·富勒键盘怎么关闭键盘winlock怎么关
- ·我买了一部OPPOA59的手机，用的是无线网络，数剧网络没开，oppor9送话费什么意思几天就没有了，请问是什么原因导致
- ·单杠20机子20码方向盘发抖式好吗
- ·这个电脑管家qq价值评估价值多少钱啊
- ·怎么设置jscharts 动态数据x轴和y轴的数据字体大小
- ·手机连接wifi不稳定有wifi想把它转到其它手机怎么办
- ·0pp0r9手机怎么截图可以用手机U盘吗?
- ·ipad4ipad分辨率怎么调调
- ·手机上出现qq邮箱已停止运行巳停止怎样删除
- ·求助 有关设置里的客制化什么意思
- ·树莓派 编程可以使用matlab编程么
- ·怎样才能在快攻中把运球运的稳一点
- ·哪里有马拉忪国外奖牌设计计教程
- ·张震岳是不是最像NBA球星的歌手张震岳
- ·为什么早上起床遗精跑步回来睡觉会遗精
