Re: SQL*Loader-908: Unable to lock table due to ORACLE error 54

John Leslie wrote:
> We have a situation where batches of data are being loaded into a
> table using sqlldr all day long. sqlldr uses direct load for speed.
At
> the same time we have a routine that deletes old records from the
> table.
>
> I believe that sqlldr needs to take an exclusive table lock...is that
> correct? Which would explain why I sometimes get Oracle 54 errors
from
> sqlldr when the delete process is running?
>
> Is my analysis of this error correct?
>
> Any ideas on resolution? I presume conventional load does not take an
> exclusive table lock?

This is from 8i since you did not specify version PLUS if you were on 9i or above... you would have been using External tables instead of sqlldr :)

"A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement." under Advantages of a Direct Path Load.

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch08.htm#912

If you are using a shell script to do this then you can run your delete immediately after the sqlldr is done e.g.

• Run sqlldr ........
• Run sqlplus ..... @Delete.sql

Regards
/Rauf Received on Tue Apr 12 2005 - 09:16:06 CDT