| Error using sqlloader [message #160758] |
Tue, 28 February 2006 04:40  |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
Does anyone know what these errors mean? i recieved this during last nights load. (We upgraded to 9.2.0.5 yesterday)
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 |
|
|
|
|
|
| Re: Error using sqlloader [message #160789 is a reply to message #160764] |
Tue, 28 February 2006 06:31  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
What that error means is: -from the docs:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e0.htm#1001560
ORA-00054 resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt because a resource was unavailable for a LOCK TABLE or SELECT FOR UPDATE command.
Action: Try the command after a few minutes or enter the command without the NOWAIT keyword.
So the table, to which you are trying to add the records, got locked...so check if anyone else has locked the table, -if so then ask that user to end his transaction and ensure that the table is no more locked (check this out querying v$locked_object etc)
and try to rerun the sql loader commands.
also rebuild the index first.
|
|
|
|
|
|
| Re: Error using sqlloader [message #160794 is a reply to message #160790] |
Tue, 28 February 2006 06:47 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Ok, but has anyone else locked this table? to find out try:
select object_name from user_objects where object_id in (select object_id from v$locked_object);
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Error using sqlloader [message #160804 is a reply to message #160801] |
Tue, 28 February 2006 07:19 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
thanks to Mahesh..I was not aware about the indexes getting unusable behavior...now one question is: after rebooting also you get these errors?:
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
|
|
|
|
| Re: Error using sqlloader [message #160806 is a reply to message #160801] |
Tue, 28 February 2006 07:23 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> Is there a way i can avoid this without changing the load method from Direct to Conventional
Depends ( Universal answer for any question in Oracle  ).
The most frequent reason for unusable indexes are duplicate keys on unique contraint column. Can you avoid it? Most probably no. In most cases the pre-process /post-process for sqlldr takes more time the load itself.If this is scripted environment, you can check for such indexes and rebuild them. Else, disabe constraints, load,enable constraints.
With direct load, you by-pass a certain RDBMS layers. So the duplicated data is NOT seen until the load is done. when sql*loader re-enables those constraints , finds some data are duplicated and so leaves the index in UNUSABLE state.
For a few certain cases, you use SKIP UNSABLE INDEXES (But not for all. Your business may not allow it).
[Updated on: Tue, 28 February 2006 07:30] Report message to a moderator |
|
|
|
| Re: Error using sqlloader [message #160813 is a reply to message #160806] |
Tue, 28 February 2006 08:04 |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
| Mahesh Rajendran wrote on Tue, 28 February 2006 07:23 |
>>Else, disabe constraints, load,enable constraints.
|
Do you mean disable constraints, run the load then enable them? When i re-enable the constraints wouldnt the same thing happen?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
