sqlloader error

chandra_1986

HI Experts,

We are getting below error:-. we need to modify the script so that,

If the script has ora error then it should load the same .csv file again and then come out of the script

SQL*Loader: Release 12.1.0.2.0 - Production on Wed May 19 09:35:04 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Control File:  las_agt2.ctl

Data File:   /opt/app/t1c32/ora01/t1c32/dba/agt2.csv

 Bad File:   ag3.bad

 Discard File: none specified

 (Allow all discards)

Number to load: ALL

Number to skip: 1

Errors allowed: 500

Continuation:  none specified

Path used:   Direct

Table report.agt_prod_stag, loaded from every logical record.

Insert option in effect for this table: TRUNCATE TRAILING NULLCOLS option in effect

  Column Name         Position  Len Term Encl Datatype

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

ID                 FIRST   *  , O(") CHARACTER

EVENTID               NEXT 4000  , O(") CHARACTER

EVENTNAME              NEXT 4000  , O(") CHARACTER

TIMESTAMP              NEXT   *  , O(") DATETIME YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"

EVENTMETHOD             NEXT 4000  , O(") CHARACTER

RESOURCENAME             NEXT 4000  , O(") CHARACTER

TARGETTYPE              NEXT 4000  , O(") CHARACTER

OPERATIONNAME            NEXT 4000  , O(") CHARACTER

FUNCTIONSTATUS            NEXT 4000  , O(") CHARACTER

PAGEID                NEXT 4000  , O(") CHARACTER

BN                  NEXT 4000  , O(") CHARACTER

JOBID                NEXT 4000  , O(") CHARACTER

RID                 NEXT 4000  , O(") CHARACTER

JOBTYPE               NEXT 4000  , O(") CHARACTER

UID                 NEXT 4000  , O(") CHARACTER

TECHREGION              NEXT 4000  , O(") CHARACTER

MGMTREPORTINGFUNCTION        NEXT 4000  , O(") CHARACTER

RECORDPUBLISHINDICATOR        NEXT 4000  , O(") CHARACTER

VERSION               NEXT 4000  , O(") CHARACTER

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table report.agt_prod_stag

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Paulzip

The script doesn't have an error, it looks like someone has issued a "select ... for update nowait" to lock record(s) in the table or issued a "lock table... in exclusive mode nowait" on the table to lock the whole table, so SQL Loader can't nuke records.

I doubt you'll be able to change your SQL loader script to solve this, it isn't a problem with the script. You may be able to try to lock the table yourself before you try the load.

chandra_1986

Thanks experts.

If we get above error, so :-

If the script has ora error then it should load the same .csv file again and then come out of the script

like here ag2.csv file should load again, how can we acheive it.

Data File:   /opt/app/t1c32/ora01/t1c32/dba/agt2.csv

James Su

Maybe you can set ddl_lock_timeout to a bigger value. This has to be done at database level since sql loader does not have an option to include "alter session".

Paulzip

Do you ever read what people say to you?

chandra_1986

Thanks experts,

I checked the value for current DDL_LOCK_TIMEOUT it's not set to any value:-

NAME                        TYPE    VALUE                                                 

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

ddl_lock_timeout                  integer   0     

what values we can set here.

James Su

you can try 60 first, meaning it will wait for one minute before the truncate if table is locked by other sessions.