SQL loader 사용시 ORA-00054 발생과 예방

SQL Loader 사용시 ora-00054 resource busy and acquire with NOWAIT specified or timeout expired 에러 발생하는 현상이 있어서 정리하였습니다.

 

우선 ORA-00054는 동일한 Object에 대해서 다른 세션이 작업 중일때 나의 세션에서 발생할 수있으며, 일반적으로 DML이나 DDL작업시 발생합니다. 문제가 발생했던 부분은 파티션테이블이었고, 각각 다른 파티션 (내가 작업하려는 파티션과 동시에 다른 파티션에서 DML이 수행중이었음)에 대한 작업이 일어나고 있는 상황에서 SQL loader 세션에서 위의 에러가 발생한 것입니다.

 

 

원인 : SQL loader 사용 시 direct=true 옵션 사용

Direct path로 동작하는 옵션 사용시, exclusive lock을 획득하게 되고 이는 가장 높은 단계의 Lock으로써 파티션 테이블이든 아니든 전체 테이블에 대한 DML lock이 하나도 없을 때 동작합니다. 즉 , DML이 있다면 기본설정시간인 30초동안은 대기하다 ORA-00054 에러를 뱉으면서 실패로 떨어지게 되는것입니다.

다른세션의 DML이 30초내에 끝나면 상관없지만 그것보다 더 걸리는 경우 에러가 발생합니다.

 

예방

1) sql*loader 형식의 작업을 할때 direct=true 옵션을 제거
해당 옵션 제거시 conventional path 방식으로 동작하게 되며, 이는 일반 DML과 동일하기 때문에 다른 파티션의 작업이라면 트랜잭션 여부에 상관없이 작업 가능합니다.

2)direct_path_lock_wait=true 을 추가
해당 옵션 추가시 30초만 기다리던 작업을 다른 세션 종료까지 대기할 수 있습니다. 그사이에 다른 트랜잭션이 종료되면 바로 exclusive lock을 획득하여 작업이 수행되게 하는 것입니다.

 

명령어

sqlldr userid=ysbae/ysbae control=/home/oracle/dba/ysbae/sqldr/emp_part.ctl direct=true direct_path_lock_wait=true

 

 

참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-commands.html

Utilities