Forum Stats

  • 3,757,262 Users
  • 2,251,216 Discussions
  • 7,869,781 Comments

Discussions

sqlloader error

chandra_1986
chandra_1986 Member Posts: 281 Blue Ribbon

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

Answers

  • Paulzip
    Paulzip Member Posts: 8,445 Blue Diamond
    edited May 19, 2021 7:43PM

    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
    chandra_1986 Member Posts: 281 Blue Ribbon

    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
    James Su Member Posts: 1,114 Gold Trophy

    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
    Paulzip Member Posts: 8,445 Blue Diamond

    Do you ever read what people say to you?

  • chandra_1986
    chandra_1986 Member Posts: 281 Blue Ribbon

    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
    James Su Member Posts: 1,114 Gold Trophy

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

    chandra_1986