Oracle 11g使用DML Error Logging來避免bulk insert故障

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語句一起使用.

--------------------------------------------------------------------------------

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

--------------------------------------------------------------------------------

為了使用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.

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁