Re: sqlldr lock while delete

Bob wrote:

> Thanks in advance,
>
> I am developing a prototype near real time system and have run into a
> problem. I have several servers sending messages via a socket to a
> central server every 30 seconds. This central server accepts a clients
> request and forks a child to handle it. The end result is processed
> data file that sqlldr loads into the database. This all works fine in
> fact flawless.
>
> The problem is that I need to run maintenance SQL and specifically I
> need to trim or delete all of the previous days data. This where the
> problem occurs. The SQL runs (PL/SQL job) locks the table and prevents
> sqlldr from loading data. I end up with a message from sqlldr saying
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-00054: resource busy and acquier with NOWAIT specified
>
> I'm not sure how to prevent this or even if its possible? Any ideas or
> work arounds?

instead of trying to "delete" the data have you considered "truncate table" which would be much faster.

I would be interested in knowing some reasoning for deleting the previous days data? doesn't that make trending a bit more difficult? I have written code that takes the data in near-real-time (5-10-30 second intervals) and puts it directly into the database as opposed to writing to a file and loading it, only to delete it in a few hours...

-- 
Michael Austin.
Consultant - Available.
Donations welcomed. http://www.firstdbasource.com/donations.html
:)