oracle 11g使用DML Error Logging来避免bulk insert故障

使用DML Error Logging来避免bulk insert故障
当使用带有子查询的insert语句来加载数据时如果出现错误.系统会终止该语句并回滚整个操作.这是非常消耗时间和资源的操作.如果insert这样的语句可以使用DML Error Logging功能来避免这种情况.

为了使用DML error logging功能需要增加一个子句来指定error logging表名来记录当DML操作所遇到的错误记录.当向insert
语句增加一个error logging子句时特定类型的错误将不会被终止和回滚语句.相反每一个错误记录会被记录到表中操作语句能继续执行.在操作完成后可以对错误记录执行修正操作.

DML error logging功能可以与insert,update,merge和delete语句一起使用.

为了使用DML error logging功能来插入数据
1.来创建一个error logging表
可以手工创建或者使用dbms_errlog包来自动创建.

2.执行一个包含error logging子句的insert语句:
.可以选择引用自己创建的error logging表.如果没有提供一个error logging表表名.数据库会使用一个缺省的error logging表.
缺省的error loggin表名为err$_后面紧接着是插入表名的前25个字符.

.可以选择包含一个标签(一个数字或者有括号的字符串)它将添加到错误日志中来帮助你识别哪个语句产生的错误.如果标签被忽略将使用null值

.可以选择包含reject limit子句
这个子句指示在insert语句终止和回滚之前可以遇到的错误的最大数量.可以指定为unlimited.缺省的reject limit为0,这意味着
在遇到第一个错误时会记录错误并回滚语句.对于并行操作reject limit应用到每一个并行服务器进程.

3.查询error logging表对于产生错误的行执行修正.

error logging表的格式
一个error logging表由两部分组成:
.描述错误的一组强制列.例如一个列包含了oracle的错误代码

.包含造成错误行数据的一组可选列.这些列名与执行插入操作的表中的列名相匹配.
error logging表中的这部分的列数可以是0,1或者多个直到与DML表中的列数相等.如果在error logging表中的列与DML表中的列
有相同的名字,那么违反插入操作的行记录中的相关数据会被写入这个error logging表中的列中.如果DML表中的列在error logging表中没有与之相关的列,那么这个列将不会记录.如果error logging表包含一个与DML表不相匹配的列那么这个列会被忽略.

因为类型转换错误是一种可能出现的错误类型,在error logging表中的可选列的数据类型必须是能捕获任何值而不会丢失数据或转换错误的数据类型.(如果可选日志列与DML表列有相同的数据类型,那么记录捕获问题数据时也会有相同的数据转换问题).数据库为了记录造成转换错误的数据的有用信息做出了最大努力.如果值不能通过派生得到,对于这个列会记录null值.插入errog logging表的一个错误会导致语句终止.

表:强制错误描述列

--------------------------------------------------------------------------------------------------------------
列名                            数据类型              描述
--------------------------------------------------------------------------------------------------------------
ora_err_number$                 number                oracle错误代码
ora_err_mesg$                   varchar2(2000)        oracle错误消息文本
ora_err_rowid$                  rowid                 错误行的rowid(对于更新和删除)
ora_err_optyp$                  varchar2(2)           操作类型:insert(i),update(u),delete(d)
                                                      注意:来自merge操作的update子句和insert子句的错误
                                                      通过U,I来区分
ora_err_tag$                    varchar2(2000)        提供给error logging子句的标签值
--------------------------------------------------------------------------------------------------------------

表:错误日志表的列数据类型

--------------------------------------------------------------------------------------------------------------
DML表列类型          错误日志表的列类型             注意
--------------------------------------------------------------------------------------------------------------
number               varchar2(4000)                 能够记录转换错误
char/varchar2(n)     varchar2(4000)                 记录没有信息丢失的任何值
nchar/nvarchar2(n)   nvarchar2(4000)                记录没有信息丢失的任何值
date/timestamp       varchar2(4000)                 记录没有信息丢失的任何值.使用缺省的date/time格式来转换成
                                                    字符格式
raw                  raw(2000)                      记录没有信息丢失的任何值
rowid                urowid                         记录任何类型的rowid
long/lob                                            不支持
用户定义数据类型                                    不支持
--------------------------------------------------------------------------------------------------------------

创建错误日志表
可以手工创建一个错误日志表或者使用pl/sql包来自动创建

使用dbms_errlog包可以自动创建一个错误日志表.create_error_log过程将创建一个有所有强制描述错误的列加上DML表中所有列
的一个错误日志表
​首先创建一个要存储数据的表test_emp

SQL> create table test_emp as select * from hr.employees where 1=2;

Table created.

SQL> alter table test_emp add primary key (employee_id);

Table altered.

先向test_temp表中插入一条记录因为让后面的插入操作产生违反主键约束的错误

SQL> insert into test_emp select * from hr.employees where rownum<2;

1 row created.

SQL> commit;

创建错误日志表

SQL> execute dbms_errlog.create_error_log('TEST_EMP','ERR_EMP');

PL/SQL procedure successfully completed.

SQL> desc err_emp
 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)
 EMPLOYEE_ID                                        VARCHAR2(4000)
 FIRST_NAME                                         VARCHAR2(4000)
 LAST_NAME                                          VARCHAR2(4000)
 EMAIL                                              VARCHAR2(4000)
 PHONE_NUMBER                                       VARCHAR2(4000)
 HIRE_DATE                                          VARCHAR2(4000)
 JOB_ID                                             VARCHAR2(4000)
 SALARY                                             VARCHAR2(4000)
 COMMISSION_PCT                                     VARCHAR2(4000)
 MANAGER_ID                                         VARCHAR2(4000)
 DEPARTMENT_ID                                      VARCHAR2(4000)

执行插入操作

SQL> insert into test_emp select * from hr.employees log errors into err_emp('test_load1') reject limit 40;

106 rows created.

SQL> insert into test_emp select * from hr.employees log errors into err_emp('test_load1') reject limit 1000;

0 rows created.

SQL> commit;

Commit complete.

查看错误信息

SQL> select * from err_emp;
 
ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                 ORA_ERR_ROWID$   ORA_ERR_OPTYP$ ORA_ERR_TAG$    EMPLOYEE_ID   FIRST_NAME         LAST_NAME            EMAIL                PHONE_NUMBER         HIRE_DATE        JOB_ID           SALARY       COMMISSION_PCT     MANAGER_ID     DEPARTMENT_ID
--------------- ------------------------------------------------------------- ---------------- -------------- --------------- ------------- ------------------ -------------------- -------------------- -------------------- ---------------- ---------------- ------------ ------------------ -------------- ------------------
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load       198           Donald             OConnell             DOCONNEL             650.507.9833         21-JUN-07        SH_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      198           Donald             OConnell             DOCONNEL             650.507.9833         21-JUN-07        SH_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      199           Douglas            Grant                DGRANT               650.507.9844         13-JAN-08        SH_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      200           Jennifer           Whalen               JWHALEN              515.123.4444         17-SEP-03        AD_ASST          4400                            101            10
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      201           Michael            Hartstein            MHARTSTE             515.123.5555         17-FEB-04        MK_MAN           13000                           100            20
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      202           Pat                Fay                  PFAY                 603.123.6666         17-AUG-05        MK_REP           6000                            201            20
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      203           Susan              Mavris               SMAVRIS              515.123.7777         07-JUN-02        HR_REP           6500                            101            40
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      204           Hermann            Baer                 HBAER                515.123.8888         07-JUN-02        PR_REP           10000                           101            70
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      205           Shelley            Higgins              SHIGGINS             515.123.8080         07-JUN-02        AC_MGR           12008                           101            110
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      206           William            Gietz                WGIETZ               515.123.8181         07-JUN-02        AC_ACCOUNT       8300                            205            110
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      100           Steven             King                 SKING                515.123.4567         17-JUN-03        AD_PRES          24000                                          90
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      101           Neena              Kochhar              NKOCHHAR             515.123.4568         21-SEP-05        AD_VP            17000                           100            90
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      102           Lex                De Haan              LDEHAAN              515.123.4569         13-JAN-01        AD_VP            17000                           100            90
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      103           Alexander          Hunold               AHUNOLD              590.423.4567         03-JAN-06        IT_PROG          9000                            102            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      104           Bruce              Ernst                BERNST               590.423.4568         21-MAY-07        IT_PROG          6000                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      105           David              Austin               DAUSTIN              590.423.4569         25-JUN-05        IT_PROG          4800                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      106           Valli              Pataballa            VPATABAL             590.423.4560         05-FEB-06        IT_PROG          4800                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      107           Diana              Lorentz              DLORENTZ             590.423.5567         07-FEB-07        IT_PROG          4200                            103            60
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      108           Nancy              Greenberg            NGREENBE             515.124.4569         17-AUG-02        FI_MGR           12008                           101            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      109           Daniel             Faviet               DFAVIET              515.124.4169         16-AUG-02        FI_ACCOUNT       9000                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      110           John               Chen                 JCHEN                515.124.4269         28-SEP-05        FI_ACCOUNT       8200                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      111           Ismael             Sciarra              ISCIARRA             515.124.4369         30-SEP-05        FI_ACCOUNT       7700                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      112           Jose Manuel        Urman                JMURMAN              515.124.4469         07-MAR-06        FI_ACCOUNT       7800                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      113           Luis               Popp                 LPOPP                515.124.4567         07-DEC-07        FI_ACCOUNT       6900                            108            100
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      114           Den                Raphaely             DRAPHEAL             515.127.4561         07-DEC-02        PU_MAN           11000                           100            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      115           Alexander          Khoo                 AKHOO                515.127.4562         18-MAY-03        PU_CLERK         3100                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      116           Shelli             Baida                SBAIDA               515.127.4563         24-DEC-05        PU_CLERK         2900                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      117           Sigal              Tobias               STOBIAS              515.127.4564         24-JUL-05        PU_CLERK         2800                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      118           Guy                Himuro               GHIMURO              515.127.4565         15-NOV-06        PU_CLERK         2600                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      119           Karen              Colmenares           KCOLMENA             515.127.4566         10-AUG-07        PU_CLERK         2500                            114            30
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      120           Matthew            Weiss                MWEISS               650.123.1234         18-JUL-04        ST_MAN           8000                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      121           Adam               Fripp                AFRIPP               650.123.2234         10-APR-05        ST_MAN           8200                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      122           Payam              Kaufling             PKAUFLIN             650.123.3234         01-MAY-03        ST_MAN           7900                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      123           Shanta             Vollman              SVOLLMAN             650.123.4234         10-OCT-05        ST_MAN           6500                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      124           Kevin              Mourgos              KMOURGOS             650.123.5234         16-NOV-07        ST_MAN           5800                            100            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      125           Julia              Nayer                JNAYER               650.124.1214         16-JUL-05        ST_CLERK         3200                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      126           Irene              Mikkilineni          IMIKKILI             650.124.1224         28-SEP-06        ST_CLERK         2700                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      127           James              Landry               JLANDRY              650.124.1334         14-JAN-07        ST_CLERK         2400                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      128           Steven             Markle               SMARKLE              650.124.1434         08-MAR-08        ST_CLERK         2200                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      129           Laura              Bissot               LBISSOT              650.124.5234         20-AUG-05        ST_CLERK         3300                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      130           Mozhe              Atkinson             MATKINSO             650.124.6234         30-OCT-05        ST_CLERK         2800                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      131           James              Marlow               JAMRLOW              650.124.7234         16-FEB-05        ST_CLERK         2500                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      132           TJ                 Olson                TJOLSON              650.124.8234         10-APR-07        ST_CLERK         2100                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      133           Jason              Mallin               JMALLIN              650.127.1934         14-JUN-04        ST_CLERK         3300                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      134           Michael            Rogers               MROGERS              650.127.1834         26-AUG-06        ST_CLERK         2900                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      135           Ki                 Gee                  KGEE                 650.127.1734         12-DEC-07        ST_CLERK         2400                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      136           Hazel              Philtanker           HPHILTAN             650.127.1634         06-FEB-08        ST_CLERK         2200                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      137           Renske             Ladwig               RLADWIG              650.121.1234         14-JUL-03        ST_CLERK         3600                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      138           Stephen            Stiles               SSTILES              650.121.2034         26-OCT-05        ST_CLERK         3200                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      139           John               Seo                  JSEO                 650.121.2019         12-FEB-06        ST_CLERK         2700                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      140           Joshua             Patel                JPATEL               650.121.1834         06-APR-06        ST_CLERK         2500                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      141           Trenna             Rajs                 TRAJS                650.121.8009         17-OCT-03        ST_CLERK         3500                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      142           Curtis             Davies               CDAVIES              650.121.2994         29-JAN-05        ST_CLERK         3100                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      143           Randall            Matos                RMATOS               650.121.2874         15-MAR-06        ST_CLERK         2600                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      144           Peter              Vargas               PVARGAS              650.121.2004         09-JUL-06        ST_CLERK         2500                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      145           John               Russell              JRUSSEL              011.44.1344.429268   01-OCT-04        SA_MAN           14000        .4                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      146           Karen              Partners             KPARTNER             011.44.1344.467268   05-JAN-05        SA_MAN           13500        .3                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      147           Alberto            Errazuriz            AERRAZUR             011.44.1344.429278   10-MAR-05        SA_MAN           12000        .3                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      148           Gerald             Cambrault            GCAMBRAU             011.44.1344.619268   15-OCT-07        SA_MAN           11000        .3                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      149           Eleni              Zlotkey              EZLOTKEY             011.44.1344.429018   29-JAN-08        SA_MAN           10500        .2                 100            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      150           Peter              Tucker               PTUCKER              011.44.1344.129268   30-JAN-05        SA_REP           10000        .3                 145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      151           David              Bernstein            DBERNSTE             011.44.1344.345268   24-MAR-05        SA_REP           9500         .25                145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      152           Peter              Hall                 PHALL                011.44.1344.478968   20-AUG-05        SA_REP           9000         .25                145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      153           Christopher        Olsen                COLSEN               011.44.1344.498718   30-MAR-06        SA_REP           8000         .2                 145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      154           Nanette            Cambrault            NCAMBRAU             011.44.1344.987668   09-DEC-06        SA_REP           7500         .2                 145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      155           Oliver             Tuvault              OTUVAULT             011.44.1344.486508   23-NOV-07        SA_REP           7000         .15                145            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      156           Janette            King                 JKING                011.44.1345.429268   30-JAN-04        SA_REP           10000        .35                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      157           Patrick            Sully                PSULLY               011.44.1345.929268   04-MAR-04        SA_REP           9500         .35                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      158           Allan              McEwen               AMCEWEN              011.44.1345.829268   01-AUG-04        SA_REP           9000         .35                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      159           Lindsey            Smith                LSMITH               011.44.1345.729268   10-MAR-05        SA_REP           8000         .3                 146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      160           Louise             Doran                LDORAN               011.44.1345.629268   15-DEC-05        SA_REP           7500         .3                 146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      161           Sarath             Sewall               SSEWALL              011.44.1345.529268   03-NOV-06        SA_REP           7000         .25                146            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      162           Clara              Vishney              CVISHNEY             011.44.1346.129268   11-NOV-05        SA_REP           10500        .25                147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      163           Danielle           Greene               DGREENE              011.44.1346.229268   19-MAR-07        SA_REP           9500         .15                147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      164           Mattea             Marvins              MMARVINS             011.44.1346.329268   24-JAN-08        SA_REP           7200         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      165           David              Lee                  DLEE                 011.44.1346.529268   23-FEB-08        SA_REP           6800         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      166           Sundar             Ande                 SANDE                011.44.1346.629268   24-MAR-08        SA_REP           6400         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      167           Amit               Banda                ABANDA               011.44.1346.729268   21-APR-08        SA_REP           6200         .1                 147            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      168           Lisa               Ozer                 LOZER                011.44.1343.929268   11-MAR-05        SA_REP           11500        .25                148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      169           Harrison           Bloom                HBLOOM               011.44.1343.829268   23-MAR-06        SA_REP           10000        .2                 148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      170           Tayler             Fox                  TFOX                 011.44.1343.729268   24-JAN-06        SA_REP           9600         .2                 148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      171           William            Smith                WSMITH               011.44.1343.629268   23-FEB-07        SA_REP           7400         .15                148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      172           Elizabeth          Bates                EBATES               011.44.1343.529268   24-MAR-07        SA_REP           7300         .15                148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      173           Sundita            Kumar                SKUMAR               011.44.1343.329268   21-APR-08        SA_REP           6100         .1                 148            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      174           Ellen              Abel                 EABEL                011.44.1644.429267   11-MAY-04        SA_REP           11000        .3                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      175           Alyssa             Hutton               AHUTTON              011.44.1644.429266   19-MAR-05        SA_REP           8800         .25                149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      176           Jonathon           Taylor               JTAYLOR              011.44.1644.429265   24-MAR-06        SA_REP           8600         .2                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      177           Jack               Livingston           JLIVINGS             011.44.1644.429264   23-APR-06        SA_REP           8400         .2                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      178           Kimberely          Grant                KGRANT               011.44.1644.429263   24-MAY-07        SA_REP           7000         .15                149            
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      179           Charles            Johnson              CJOHNSON             011.44.1644.429262   04-JAN-08        SA_REP           6200         .1                 149            80
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      180           Winston            Taylor               WTAYLOR              650.507.9876         24-JAN-06        SH_CLERK         3200                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      181           Jean               Fleaur               JFLEAUR              650.507.9877         23-FEB-06        SH_CLERK         3100                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      182           Martha             Sullivan             MSULLIVA             650.507.9878         21-JUN-07        SH_CLERK         2500                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      183           Girard             Geoni                GGEONI               650.507.9879         03-FEB-08        SH_CLERK         2800                            120            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      184           Nandita            Sarchand             NSARCHAN             650.509.1876         27-JAN-04        SH_CLERK         4200                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      185           Alexis             Bull                 ABULL                650.509.2876         20-FEB-05        SH_CLERK         4100                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      186           Julia              Dellinger            JDELLING             650.509.3876         24-JUN-06        SH_CLERK         3400                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      187           Anthony            Cabrio               ACABRIO              650.509.4876         07-FEB-07        SH_CLERK         3000                            121            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      188           Kelly              Chung                KCHUNG               650.505.1876         14-JUN-05        SH_CLERK         3800                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      189           Jennifer           Dilly                JDILLY               650.505.2876         13-AUG-05        SH_CLERK         3600                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      190           Timothy            Gates                TGATES               650.505.3876         11-JUL-06        SH_CLERK         2900                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      191           Randall            Perkins              RPERKINS             650.505.4876         19-DEC-07        SH_CLERK         2500                            122            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      192           Sarah              Bell                 SBELL                650.501.1876         04-FEB-04        SH_CLERK         4000                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      193           Britney            Everett              BEVERETT             650.501.2876         03-MAR-05        SH_CLERK         3900                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      194           Samuel             McCain               SMCCAIN              650.501.3876         01-JUL-06        SH_CLERK         3200                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      195           Vance              Jones                VJONES               650.501.4876         17-MAR-07        SH_CLERK         2800                            123            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      196           Alana              Walsh                AWALSH               650.507.9811         24-APR-06        SH_CLERK         3100                            124            50
              1 ORA-00001: unique constraint (SYS.SYS_C0014361) violated                       I              test_load1      197           Kevin              Feeney               KFEENEY              650.507.9822         23-MAY-06        SH_CLERK         3000                            124            50
 
108 rows selected
 

从err_emp表中的记录可以看到不能插入test_emp的记录的错误信息是因为违反主键约束.

error logging的限制和警告
当执行DML操作时出现以下错误时oracle数据库会进行记录:
.列的值太长
.违反约束(not null,unique,referential,check)
.在触发器执行时触发的错误
.在子查询列与DML表相关列之间类型转换的错误
.分区映射错误
.特定的merge操作错误(ora-30926:Unable to get a stable set of rows for MERGE operation)

有一些错误不会记录且会造成DML操作终止和回滚.

对union集合操作理解不足造成的巨大的灾难

朋友和我说发现了oracle union操作的一个bug,他的查询语句如下:

SQL> select sum(a1) a1,sum(a2) a2
  2  from (
  3  select 1 a1,0 a2 from dual
  4  union
  5  select 0 a2, 1 a1 from dual
  6  )
  7  ;

        A1         A2
---------- ----------
         1          1


SQL> select 1 a1,0 a2 from dual
  2  union
  3  select 0 a2, 1 a1 from dual
  4  ;

        A1         A2
---------- ----------
         0          1
         1          0

说结果出错了,应该a2为0,他说别名为 a2的值其实是0,说union不是按别名来进行区别的,一开始我还没反应过来。后面想了一下,union的意思是用来合并两条查询的结果并去重,在oracle关于sql的文档中是说union集合操作中的两个查询中的列的类型和个数要相同。例如将上面两个查询别名为a2的值从0改成***217;0***217;再执行union操作就会报错:

SQL> select 1 a1,'0' a2 from dual
  2  union
  3  select '0' a2, 1 a1 from dual
  4  ;

select 1 a1,'0' a2 from dual
union
select '0' a2, 1 a1 from dual

ORA-01790: 表达式必须具有与对应表达式相同的数据类型

从而说明了union其实是按列出现的顺序进行合并的,并不是按别名.对union的意思理解不对危害很大,从上面的列来说他本来的意思是a2应该为0,但因为他的顺序写错了结果变成了1。这种危害太大了,如果这些数字是钱本来应该为0的,结果因为查询中列的顺序写错了,就会造成巨大的损失。而朋友的系统就是这么巧就是money,且软件运行了有些年头了。

oracle的并行管道函数

并行管道函数
这个例子中要使用两个表:T1和T2。T1是先读的表,T2表用来插入这个信息。我们要用的两个表如下:

sys@JINGYONG> create table t1
  2  as
  3  select object_id id,object_name text
  4  from all_objects;

表已创建。

sys@JINGYONG> begin
  2  dbms_stats.set_table_stats
  3  (user,'T1',numrows=>100000000,numblks=>100000);
  4  end;
  5  /

PL/SQL 过程已成功完成。

sys@JINGYONG> create table t2
  2  as
  3  select t1.*,0 session_id
  4  from t1
  5  where 1=0;

表已创建。

这里使用DBMS_STATS来骗过优化器,让它以为输入表中有10,000,000行,而且占用了100,000个数据库块。在此模拟 一个大表。第二个表T2是第一个表的一个副本,只是在结构中增加了一个SESSION_ID列。可以通过它具体看到是否发生了并行化。接下来,需要建立管道函数返回的对象类型。在这个例子中,对象类型类似于T2:

sys@JINGYONG> create or replace type t2_type
  2  as object
  3  (
  4  id number,
  5  text varchar2(30),
  6  session_id number
  7  );
  8  /

类型已创建。

sys@JINGYONG> create or replace type t2_tab_type as table of t2_type;
  2  /

类型已创建。

现在这个过程是一个生成行的函数。它接收数据作为输入,并在一个引用游标(ref cursor)中处理。这个函数返回一个 T2_TAB_TYPE,这就是我们刚才创建的对象类型。这是一个PARALLEL_ENABLED(启用子并行)的管道函数。在此使用了分区 (partition)子句,这就告诉Oracle:以任何最合适的方式划分或分解数据。我们不需要对数据的顺序做任何假设。

在此,我们只想划分数据。数据如何划分对于我们的处理并不重要,所以定义如下:

sys@JINGYONG> create or replace function parallel_pipelined(l_cursor in sys_refcursor)
  2  return t2_tab_type
  3  pipelined
  4  parallel_enable(partition l_cursor by any)
  5  is
  6   l_session_id number;
  7   TYPE type_t1_data IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
  8   l_t1  type_t1_data;
  9
 10  begin
 11  select sid into l_session_id
 12  from v$mystat
 13  where rownum=1;
 14  loop
 15    fetch l_cursor bulk collect into l_t1;--用bulk collect来一次性获取数据
 16    exit when l_t1.count=0;
 17    for i in 1 .. l_t1.count loop
 18          pipe row(t2_type(l_t1(i).id,l_t1(i).text,l_session_id));
 19    end loop;
 20    null;
 21  end loop;
 22  close l_cursor;
 23  return;
 24  end;
 25  /

Function created

或者用下面的过程来一行一行来获取

create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
return t2_tab_type
pipelined
parallel_enable ( partition l_cursor by any )
is
 l_session_id number;
 l_rec t1%rowtype;
begin
 select sid into l_session_id
 from v$mystat
 where rownum =1;
 loop
 fetch l_cursor into l_rec;
 exit when l_cursor%notfound;
 pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
 end loop;
 close l_cursor;
 return;
end;

这样就创建了函数。我们准备并行地处理数据,让Oracle根据可用的资源来确定最合适的并行度:

SQL> insert /*+ append */
  2  into t2(id,text,session_id)
  3   select *
  4   from table(parallel_pipelined
  5   (CURSOR(select /*+ parallel(t1) */ *
  6   from t1 )
  7  ))
  8  ;

50333 rows inserted

SQL> commit;

Commit complete

为了查看这里发生了什么,可以查询新插入的数据,并按SESSION_ID分组,先来看使用了多少个并行执行服务器,再看每个并行 执行服务器处理了多少行:

SQL> select session_id,count(*) from t2 group by session_id;

SESSION_ID   COUNT(*)
---------- ----------
       136      31006
       145      19327

显然,对于这个并行操作的SELECT部分,我们使用了2个并行执行服务器,可以看到,Oracle对我们的过程进行了并行化

自定义show_space过程来显示数据段的利用信息

SHOW_SPACE过程
SHOW_SPACE例程用于打印数据库段空间利用率信息:

sys@DEVELOP> create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner in varchar2 default user,
  4    p_type in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  -- this procedure uses authid current user so it can query DBA_*
  7  -- views using privileges from a ROLE, and so it can be installed
  8  -- once per database, instead of once per user who wanted to use it
  9    authid current_user
 10  as
 11    l_free_blks number;
 12    l_total_blocks number;
 13    l_total_bytes number;
 14    l_unused_blocks number;
 15    l_unused_bytes number;
 16    l_LastUsedExtFileId number;
 17    l_LastUsedExtBlockId number;
 18    l_LAST_USED_BLOCK number;
 19    l_segment_space_mgmt varchar2(255);
 20    l_unformatted_blocks number;
 21    l_unformatted_bytes number;
 22    l_fs1_blocks number; l_fs1_bytes number;
 23    l_fs2_blocks number; l_fs2_bytes number;
 24    l_fs3_blocks number; l_fs3_bytes number;
 25    l_fs4_blocks number; l_fs4_bytes number;
 26    l_full_blocks number; l_full_bytes number;
 27  -- inline procedure to print out numbers nicely formatted
 28  -- with a simple label
 29  procedure p( p_label in varchar2, p_num in number )
 30  is
 31  begin
 32    dbms_output.put_line( rpad(p_label,40,'.') ||
 33    to_char(p_num,'999,999,999,999') );
 34  end;
 35  begin
 36  -- this query is executed dynamically in order to allow this procedure
 37  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
 38  -- via a role as is customary.
 39  -- NOTE: at runtime, the invoker MUST have access to these two
-- views!
 40   41  -- this query determines if the object is an ASSM object or not
 42    begin
 43      execute immediate
 44      'select ts.segment_space_management
 45      from dba_segments seg, dba_tablespaces ts
 46      where seg.segment_name = :p_segname
    and (:p_partition is null or
 47   48      seg.partition_name = :p_partition)
 49      and seg.owner = :p_owner
 50      and seg.tablespace_name = ts.tablespace_name'
 51      into l_segment_space_mgmt
 52      using p_segname, p_partition, p_partition, p_owner;
 53    exception
 54    when too_many_rows then
 55      dbms_output.put_line
 56        ( 'This must be a partitioned table, use p_partition => ');
 57      return;
 58    end;
 59  -- if the object is in an ASSM tablespace, we must use this API
 60  -- call to get space information, otherwise we use the FREE_BLOCKS
 61  -- API for the user-managed segments
 62    if l_segment_space_mgmt = 'AUTO'   then
 63       dbms_space.space_usage
 64       ( p_owner, p_segname, p_type, l_unformatted_blocks,
 65       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 66       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 67       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 68       p( 'Unformatted Blocks ', l_unformatted_blocks );
 69       p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
 70       p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
 71       p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
 72       p( 'FS4 Blocks (75-100)', l_fs4_blocks );
 73       p( 'Full Blocks ', l_full_blocks );
 74    else
 75       dbms_space.free_blocks(
 76       segment_owner => p_owner,
 77       segment_name => p_segname,
 78       segment_type => p_type,
 79       freelist_group_id => 0,
 80       free_blks => l_free_blks);
 81       p( 'Free Blocks', l_free_blks );
 82    end if;
 83  -- and then the unused space API call to get the rest of the
 84  -- information
 85    dbms_space.unused_space
 86     ( segment_owner => p_owner,
 87       segment_name => p_segname,
 88       segment_type => p_type,
 89       partition_name => p_partition,
 90       total_blocks => l_total_blocks,
 91       total_bytes => l_total_bytes,
 92       unused_blocks => l_unused_blocks,
 93       unused_bytes => l_unused_bytes,
 94       LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 95       LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 96       LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 97       p( 'Total Blocks', l_total_blocks );
 98       p( 'Total Bytes', l_total_bytes );
 99       p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
100       p( 'Unused Blocks', l_unused_blocks );
101       p( 'Unused Bytes', l_unused_bytes );
102       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
103       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
104       p( 'Last Used Block', l_LAST_USED_BLOCK );
105  end;
106  /

Procedure created.

show_space过程包含以下参数:

sys@DEVELOP> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT

参数如下:
• P_SEGNAME:段名(例如,表或索引名)。
• P_OWNER:默认为当前用户,不过也可以使用这个例程查看另外某个用户。
• P_TYPE:默认为TABLE,这个参数表示查看哪种类型的对象(段)。例如,SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS
会列出合法的段类型。
• P_PARTITION:显示分区对象的空间时所用的分区名。SHOW_SPACE一次只显示一个分区的空间利用率。

这个过程的输出如下,这里段位于一个自动段空间管理(Automatic Segment Space Management, ASSM)表空间中:

sys@DEVELOP> exec show_space('AC01','HNSIC');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              12
Full Blocks ............................          16,305
Total Blocks............................         146,304
Total Bytes.............................   1,198,522,368
Total MBytes............................           1,143
Unused Blocks...........................         129,920
Unused Bytes............................   1,064,304,640
Last Used Ext FileId....................              10
Last Used Ext BlockId...................         348,800
Last Used Block.........................           8,192

PL/SQL procedure successfully completed.

报告的各项结果说明如下:
• Unformatted Blocks:为表分配的位于高水位线(high-water mark, HWM)之下但未用的块数。把未格式化和未用的块加在一起,就是已为表分配但从未用于保存ASSM对象数据的总块数。
• FS1 Blocks-FS4 Blocks:包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。例如,(0-25)是指空闲度为
0~25%的块数。
• Full Blocks:已满的块数,不能再对这些执行插入。
• Total Blocks、Total bytes、Total Mbytes:为所查看的段分配的总空间量,单位分别是数据库块、字节和兆字节。
• Unused Blocks、Unused Bytes:表示未用空间所占的比例(未用空间量)。这些块已经分配给所查看的段,但目前在段的HWM之上。
• Last Used Ext FileId:最后使用的文件的文件ID,该文件包含最后一个含数据的区段(extent)。
• Last Used Ext BlockId:最后一个区段开始处的块ID;这是最后使用的文件中的块ID。
• Last Used Block:最后一个区段中最后一个块的偏移量。

如果对象在用户空间管理的表空间中,使用SHOW_SPACE查看时,输出如下:

sys@DEVELOP> exec show_space('T1');
Free Blocks.............................               3
Total Blocks............................          17,408
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             869
Unused Bytes............................       7,118,848
Last Used Ext FileId....................               1
Last Used Ext BlockId...................       1,696,896
Last Used Block.........................             155

PL/SQL procedure successfully completed.

这里惟一的区别是报告中最前面的Free Blocks项。这是段的第一个freelist(自由列表)组中的块数。
脚本只测试了第一个freelist组。如果想测试多个freelist组,还需要修改这个脚本。

自定义统计结果脚本mystat

mystat.sql和相应的mystat2.sql用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。mystat.sql只是获得统计结果的开始值:
mystat.sql脚本内容如下:

set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

mystat2.sql用于报告统计结果的变化情况(差值):

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

例如,要查看某个UPDATE生成的redo数,可以使用以下命令:

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat.sql "redo size"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         756293676
redo size for lost write detection                                        0
redo size for direct writes                                               0

sys@DEVELOP> update t1 set aac009='1';

795680 rows updated.

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat2.sql

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size                                                        1139306496    1,139,306,496
redo size for lost write detection                                        0                0
redo size for direct writes                                               0                0

由此可见,795,680行记录的UPDATE会生成1,139,306,496字节的redo.

Proudly powered by WordPress | Indrajeet by Sus Hill.