대랑 INSERT UPDATE MERGE DML 작업시 오류 로그 방법

아래 방법을 찾아본 이유는 대량  MERGE 작업 시 PK오류가 발생하면서 부터다.

PK오류를 방지하기 위해서 MERGE를 썼는데 . MERGE에서 SELECT 하는 시간 동안 다른 트래잭션이 먼저 들어 왔었나보다.

그래서 통째로 롤백되고 말았다. 

아래 방법으로  해결했다.

대량 INSERT 실패를 재치 시간 DML 오류 로깅 방지

당신이 사용하는 테이블을로드 할 때 INSERT의 하위 쿼리와 문을 오류가 발생하면, 문은 종료되고 전체가 롤백됩니다. 이것은 시간과 시스템 자원의 낭비가 될 수 있습니다. 예를 들어 INSERT의 문, 당신은 DML 오류 로깅 기능을 사용하여 이러한 상황을 방지 할 수 있습니다.

DML 오류 로깅을 사용하려면 데이터베이스가 DML 작업 중에 발생한 오류를 기록되는 오류 로깅 테이블의 이름을 지정하는 문 절을 추가합니다. 당신이이 오류 로깅 절을 추가 할 때 INSERT의 성명, 특정 유형의 오류는 더 이상 종료하지 않고 문을 롤백. 대신, 각 오류가 기록과 문은 계속됩니다. 그런 다음 나중에 잘못된 행에 시정 조치를 취합니다.

DML 오류 로깅이 작동 INSERT , UPDATE는 , MERGE 및 DELETE 문을. 이 섹션에 초점을 맞추고 삽입 문.

오류 로깅 데이터를 삽입하려면 :

1. 오류 로깅 테이블을 만듭니다. (Optional) (선택 과목)

   당신은 테이블을 수동으로 작성하거나 사용할 수 있습니다 DBMS_ERRLOG에 자동으로 당신을 위해 그것을 만들 패키지를. 를 참조하십시오 "오류 로깅 테이블 만들기" 자세한 내용은.

2. 실행 INSERT의 문을 오류 로깅 절을 포함한다. 이 절 :

   • 선택적으로 사용자가 만든 오류 로깅 표를 참조합니다. 오류 로깅 테이블 이름, 기본 이름에 오류 로깅 테이블에 데이터베이스 로그를 제공하지 않는 경우. 기본 오류 로깅 테이블의 이름입니다 ERR $ _ 에 삽입되는 테이블의 이름의 처음 25 자 하였다.

   • 선택적으로 포함 태그 (숫자 또는 문자열 괄호 문자) 오류를 발생시킨 문을 식별하는 데 도움이 오류 로그에 추가됩니다. 태그가 생략되는 경우 NULL의 값이 사용된다.

   • 선택적으로 포함 LIMIT의 REJECT 절을.

     이 절은 전에 발생할 수있는 오류의 최대 수를 나타냅니다 INSERT의 문을 종료하고 롤백을. 또한 지정할 수 있습니다 UNLIMITED . 기본값은 제한이 첫 번째 오류가 발생시 오류가 기록됩니다 즉, 제로 거부하고 문을 롤백합니다. 병렬 DML 작업 들어, 제한은 각각의 병렬 서버에 적용됩니다 거부합니다.

   노트:

   문이 (가) 제한을 거부 초과하고 롤백 경우, 오류 로그 테이블은 로그 항목은 지금까지 기록 유지한다.

   참조 오라클 데이터베이스 SQL 언어 참조 오류 로깅 절 구문 정보를.

3. 오류 로그 테이블을 쿼리 오류를 생성 한 행에 대한 시정 조치를 취할 수 있습니다.

   를 참조하십시오 "오류 로깅 표 형식" 오류 로그 테이블 구조에 대한 자세한 내용은이 섹션 뒷부분의.

예 다음 문은에 행을 삽입 DW_EMPL의 테이블과 오류를 기록 ERR_EMPL의 테이블. 태그 ' daily_load는 '각 로그 항목에 복사됩니다. 성명은 종료하고 오류의 수가 25을 초과하는 경우 롤백합니다.

INSERT INTO dw_empl INSERT INTO의 dw_empl
  SELECT employee_id, first_name, last_name, hire_date, salary, department_id SELECT employee_id입니다, FIRST_NAME, LAST_NAME, hire_date, 급여, department_id 
  FROM employees 직원 FROM
  WHERE hire_date > sysdate - 7 WHERE hire_date> SYSDATE - 7
  LOG ERRORS INTO err_empl ('daily_load') REJECT LIMIT 25 err_empl ( 'daily_load') LIMIT (25)을 거부 INTO 오류를 기록

더 많은 예제에 대한 내용은 오라클 데이터베이스 SQL 언어 참조 및 오라클 데이터베이스 데이터웨어 하우징 가이드 .

Error Logging Table Format 오류 로깅 테이블 형식

오류 로깅 테이블은 두 부분으로 구성되어 있습니다 :

• 오류를 설명하는 컬럼의 필수입니다. 예를 들어, 하나의 열은 오라클 오류 번호가 포함되어 있습니다.

  표 20-3은 이러한 오류 설명 열을 나열합니다.

• 오류가 발생한 행의 데이터가 포함 된 열 옵션을 설정합니다. 열 이름은 테이블에서 열 이름 (이하 "DML 테이블")에 삽입 일치합니다.

  오류 로그 테이블의이 부분의 열 수는 DML 테이블의 열 수까지 0, 1, 이상이 될 수 있습니다. 열이 DML 테이블의 컬럼과 동일한 이름을 가진 오류 로깅 테이블에 존재하는 경우, 삽입되는 잘못된 행에서 해당 데이터가이 오류 로깅 테이블 컬럼에 기록됩니다. DML 테이블 열은 에러 로그 테이블에서 해당 열이 없으면, 열이 기록되지 않는다.  오류 로그 테이블에 DML 테이블 컬럼과 일치하지 않는 이름을 가진 열을 포함하는 경우, 열이 무시됩니다.

  형식 변환 오류가 발생할 수있는 오류의 일종이기 때문에, 에러 로그 테이블의 임의 컬럼의 데이터 형식은 데이터의 손실 또는 변환 에러없이 임의의 값을 포착 할 수 종류이어야한다. (옵션 로그 열이 오류가 발생한 동일한 데이터 변환 문제를 입을 수 로그에 문제가있는 데이터를 캡처의 DML 테이블 컬럼과 같은 종류의라면.) 데이터베이스는 데이터에 대한 의미있는 값을 기록 할 수있는 최선의 노력을 기울이고 그 변환 오류가 발생합니다. 값을 도출 할 수없는 경우, NULL는 열에 기록됩니다. 오류 로그 테이블에 삽입에 오류가 문이 종료됩니다.

  표 20-4은 DML 테이블에서 각 데이터 유형에 대해 사용하도록 권장 오류 로깅 테이블 컬럼 데이터 유형을 나열합니다. 당신이 자동으로 오류 로그 테이블을 작성할 때이 권장 데이터 형식이 사용됩니다 DBMS_ERRLOG의 패키지로 제공된다.

표 20-3 필수 오류 설명 열

열 이름	데이터 형식	기술
ORA_ERR_NUMBER $	번호	Oracle error number 오라클 오류 번호
ORA_ERR_MESG $	VARCHAR2 (2000)	Oracle error message text 오라클 오류 메시지 텍스트
ORA_ERR_ROWID $	ROWID	Rowid of the row in error (for update and delete) 행 오류 (업데이트 및 삭제)의 ROWID
ORA_ERR_OPTYP $	VARCHAR2 (2)	작업의 종류 : 삽입 ( I ), 갱신 ( U , 삭제) ( D ) 참고 : 업데이트 절에서 오류와 절 삽입 MERGE의 동작에 의해 구별된다 U 와 I 값.
ORA_ERR_TAG $	VARCHAR2 (2000)	오류 로깅 절에 사용자가 제공 한 태그의 값

표 20-4 오류 로깅 테이블 열 데이터 형식

테이블 열 유형	오류 로깅 테이블 열 유형	노트
번호	VARCHAR2 (4000)	변환 오류를 기록 할 수
CHAR / VARCHAR2 (N)	VARCHAR2 (4000)	정보 손실없이 값을 기록합니다
NCH​​AR / NVARCHAR2 (N)	NVARCHAR2 (4000)	정보 손실없이 값을 기록합니다
DATE / TIMESTAMP	VARCHAR2 (4000)	정보 손실없이 값을 기록합니다. 디폴트 날짜 / 시간 형식 마스크 문자 형식으로 변환
노골적인	RAW (2000)	정보 손실없이 값을 기록합니다
ROWID	UROWID	어떤 ROWID 유형을 기록합니다
LONG / LOB		지원되지 않음
User-defined types 사용자 정의 유형		지원되지 않음

오류 로깅 테이블 만들기

수동으로 오류 로깅 표를 만들거나 자동으로 당신을 위해 하나를 만들기 위해 PL / SQL 패키지를 사용할 수 있습니다.

자동으로 오류 로깅 테이블 만들기

당신은 사용 DBMS_ERRLOG의 자동 에러 로깅 테이블을 만들 패키지를. CREATE_ERROR_LOG의 절차에 표시된 데이터 유형 맵핑 명명 된 DML 테이블의 컬럼의 필수 오류 설명 열을 모두 더한 값에 오류 로깅 테이블을 생성하고, 수행 표 20-4을 .

다음 문은 앞의 예에서 사용 된 오류 로그 테이블을 작성합니다.

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('DW_EMPL', 'ERR_EMPL'); DBMS_ERRLOG.CREATE_ERROR_LOG ( 'DW_EMPL', 'ERR_EMPL를')을 실행;

참조 오라클 데이터베이스 PL / SQL 패키지 및 유형 참조 에 대한 자세한 내용은 DBMS_ERRLOG을 .

표를 수동으로 로그인 오류 만들기

수동으로 오류 로그 테이블을 작성 표준 DDL을 사용합니다. 를 참조하십시오 "오류 로깅 테이블 형식" 테이블 구조 요구 사항. 당신은 모든 필수 오류 설명 열을 포함해야합니다. 이들은 임의의 순서로 될 수 있지만, 테이블의 첫 번째 열이 있어야한다.

오류 로깅 제한 및주의 사항

오라클 데이터베이스는 DML 작업 중에 다음과 같은 오류를 기록합니다

• 너무 큰 열 값

• 제약 조건 위반 ( NOT NULL , 고유, 참조 및 점검 제한 조건)

• 오류 트리거 실행 중에 발생

• 하위 쿼리의 열 사이의 유형 변환으로 인한 오류 및 테이블의 해당 열

• 파티션 매핑 에러

• 특정 MERGE의 작동 오류 ( ORA-30926 : MERGE 작업에 대한 행의 안정적인 세트를 가져올 수 없습니다.)

약간의 오차가 종료하고 롤백하는 DML 작업을 기록, 원인이되지 않습니다. 이러한 오류의 다른 DML 로깅 제한에 대한 목록은의 설명 참조 error_logging_clause을 에 삽입 섹션 오라클 데이터베이스 SQL 언어 참조 .

Space Considerations 공간 고려 사항

당신이 DML 오류 로깅을 사용하기 전에 공간 요구 사항을 고려 있는지 확인합니다. 당신은뿐만 아니라 테이블이뿐만 아니라 오류 로깅 테이블에 삽입 가능한 공간이 필요합니다.

Security 보안

문제 사용자 삽입 DML 오류 로깅 문이 있어야합니다 INSERT의 오류 로그 테이블에 권한을.

See Also: 참조 :

오라클 데이터베이스 SQL 언어 참조 및 오라클 데이터베이스 데이터웨어 하우징 가이드 DML 오류 로깅 예제.

원문 ==========>

Avoiding Bulk INSERT Failures with DML Error Logging

When you load a table using an INSERT statement with subquery, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.

To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.

To insert data with DML error logging:

1. Create an error logging table. (Optional)

   You can create the table manually or use the DBMS_ERRLOG package to automatically create it for you. See "Creating an Error Logging Table" for details.

2. Execute an INSERT statement and include an error logging clause. This clause:

   • Optionally references the error logging table that you created. If you do not provide an error logging table name, the database logs to an error logging table with a default name. The default error logging table name is ERR$_ followed by the first 25 characters of the name of the table that is being inserted into.

   • Optionally includes a tag (a numeric or string literal in parentheses) that gets added to the error log to help identify the statement that caused the errors. If the tag is omitted, a NULL value is used.

   • Optionally includes a REJECT LIMIT subclause.

     This subclause indicates the maximum number of errors that can be encountered before the INSERT statement terminates and rolls back. You can also specify UNLIMITED. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back. For parallel DML operations, the reject limit is applied to each parallel server.

   Note:

   If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.

   See Oracle Database SQL Language Reference for error logging clause syntax information.

3. Query the error logging table and take corrective action for the rows that generated errors.

   See "Error Logging Table Format", later in this section, for details on the error logging table structure.

Example The following statement inserts rows into the DW_EMPL table and logs errors to the ERR_EMPL table. The tag 'daily_load' is copied to each log entry. The statement terminates and rolls back if the number of errors exceeds 25.

INSERT INTO dw_empl
  SELECT employee_id, first_name, last_name, hire_date, salary, department_id 
  FROM employees
  WHERE hire_date > sysdate - 7
  LOG ERRORS INTO err_empl ('daily_load') REJECT LIMIT 25

For more examples, see Oracle Database SQL Language Reference and Oracle Database Data Warehousing Guide.

Error Logging Table Format

The error logging table consists of two parts:

• A mandatory set of columns that describe the error. For example, one column contains the Oracle error number.

  Table 20-3 lists these error description columns.

• An optional set of columns that contain data from the row that caused the error. The column names match the column names from the table being inserted into (the "DML table").

  The number of columns in this part of the error logging table can be zero, one, or more, up to the number of columns in the DML table. If a column exists in the error logging table that has the same name as a column in the DML table, the corresponding data from the offending row being inserted is written to this error logging table column. If a DML table column does not have a corresponding column in the error logging table, the column is not logged. If the error logging table contains a column with a name that does not match a DML table column, the column is ignored.

  Because type conversion errors are one type of error that might occur, the data types of the optional columns in the error logging table must be types that can capture any value without data loss or conversion errors. (If the optional log columns were of the same types as the DML table columns, capturing the problematic data into the log could suffer the same data conversion problem that caused the error.) The database makes a best effort to log a meaningful value for data that causes conversion errors. If a value cannot be derived, NULL is logged for the column. An error on insertion into the error logging table causes the statement to terminate.

  Table 20-4 lists the recommended error logging table column data types to use for each data type from the DML table. These recommended data types are used when you create the error logging table automatically with the DBMS_ERRLOG package.

Table 20-3 Mandatory Error Description Columns

Column Name	Data Type	Description
ORA_ERR_NUMBER$	NUMBER	Oracle error number
ORA_ERR_MESG$	VARCHAR2(2000)	Oracle error message text
ORA_ERR_ROWID$	ROWID	Rowid of the row in error (for update and delete)
ORA_ERR_OPTYP$	VARCHAR2(2)	Type of operation: insert (I), update (U), delete (D) Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.
ORA_ERR_TAG$	VARCHAR2(2000)	Value of the tag supplied by the user in the error logging clause

Table 20-4 Error Logging Table Column Data Types

DML Table Column Type	Error Logging Table Column Type	Notes
NUMBER	VARCHAR2(4000)	Able to log conversion errors
CHAR/VARCHAR2(n)	VARCHAR2(4000)	Logs any value without information loss
NCHAR/NVARCHAR2(n)	NVARCHAR2(4000)	Logs any value without information loss
DATE/TIMESTAMP	VARCHAR2(4000)	Logs any value without information loss. Converts to character format with the default date/time format mask
RAW	RAW(2000)	Logs any value without information loss
ROWID	UROWID	Logs any rowid type
LONG/LOB		Not supported
User-defined types		Not supported

Creating an Error Logging Table

You can create an error logging table manually, or you can use a PL/SQL package to automatically create one for you.

Creating an Error Logging Table Automatically

You use the DBMS_ERRLOG package to automatically create an error logging table. The CREATE_ERROR_LOG procedure creates an error logging table with all of the mandatory error description columns plus all of the columns from the named DML table, and performs the data type mappings shown in Table 20-4.

The following statement creates the error logging table used in the previous example.

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('DW_EMPL', 'ERR_EMPL');

See Oracle Database PL/SQL Packages and Types Reference for details on DBMS_ERRLOG.

Creating an Error Logging Table Manually

You use standard DDL to manually create the error logging table. See "Error Logging Table Format" for table structure requirements. You must include all mandatory error description columns. They can be in any order, but must be the first columns in the table.

Error Logging Restrictions and Caveats

Oracle Database logs the following errors during DML operations:

• Column values that are too large

• Constraint violations (NOT NULL, unique, referential, and check constraints)

• Errors raised during trigger execution

• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table

• Partition mapping errors

• Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

Some errors are not logged, and cause the DML operation to terminate and roll back. For a list of these errors and for other DML logging restrictions, see the discussion of the error_logging_clause in the INSERT section of Oracle Database SQL Language Reference.

Space Considerations

Ensure that you consider space requirements before using DML error logging. You require available space not only for the table being inserted into, but also for the error logging table.

Security

The user who issues the INSERT statement with DML error logging must have INSERT privileges on the error logging table.

See Also:

Oracle Database SQL Language Reference and Oracle Database Data Warehousing Guide for DML error logging examples.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11656