ku916
asked on
NOWAIT error when appending with SQL LOADER
I've this process that would perform multiple sql load into the same table with append option. It was working fine and then I get this error in 1 of my load.
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table OWNER.SCORES_TEMP, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
--------------------------
ID FIRST * | O(") CHARACTER
HSE NEXT * | O(") CHARACTER
PROB NEXT * | O(") CHARACTER
MODEL NEXT * | O(") CHARACTER
SCORE_DATE NEXT * | O(") DATE YYYY-MM-DD
SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified
I;ve read that update could cause this error, but haven't found anything on sql loading. What to do?
Ku
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table OWNER.SCORES_TEMP, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
--------------------------
ID FIRST * | O(") CHARACTER
HSE NEXT * | O(") CHARACTER
PROB NEXT * | O(") CHARACTER
MODEL NEXT * | O(") CHARACTER
SCORE_DATE NEXT * | O(") DATE YYYY-MM-DD
SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified
I;ve read that update could cause this error, but haven't found anything on sql loading. What to do?
Ku
anand_2000v
most probably there is another session doing some DML activities on the table. Make sure that no other user is connected and try again
ASKER
There aren't any users using the systems. this is in staging where the whole thing is lock-down.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check in v$locked_objects whether any objects are locked and what are the sid which are locking them. Kill then sessions and their OS processes or do a restart of the DB. It might also be that a previous session of SQL*loader is locking the object.
Is there any trigger on the affected table? If it's, then check it's status.
Regards,
Bob
Regards,
Bob
Hi there !
Start ur SQL*Loader process, run the following query (in SQL*PLUS) immediately:
select (select username from v$session where sid=a.sid) blocker,
a.sid, 'is blocking',(select username from v$session where sid=b.sid) Blockee, b.sid
from V$lock a , v$lock b
where a.block=1
and b.request > 1
and a.id1=b.id1
and a.id2 = b.id2
/
It will tell you who is blocking who.
Riaz
Start ur SQL*Loader process, run the following query (in SQL*PLUS) immediately:
select (select username from v$session where sid=a.sid) blocker,
a.sid, 'is blocking',(select username from v$session where sid=b.sid) Blockee, b.sid
from V$lock a , v$lock b
where a.block=1
and b.request > 1
and a.id1=b.id1
and a.id2 = b.id2
/
It will tell you who is blocking who.
Riaz