Re: sqlldr lock while delete

Michael Austin <maustin_at_firstdbasource.com> wrote in message news:<Y3wLc.16544$2L6.1912_at_newssvr23.news.prodigy.com>...
> 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...

thanks for the reply,

I'm writting to a file because I am trying to filter the data into different categories so the loads are based on for example service data, system data and networking data. The source is the same its just that the output after the filter is data in different buckets

How are you putting your data directly into the database? Are these just inserts or updates in code like perl of java?

I think your probably right with the truncate approach. I wanted to delete the previous days worth of data, in fact was probably gunna leave a rolling weeks worth of data available for query, but it can be summarized and moved around.

I found though that in the sqlldr load statement if I did not use direct=true things worked just fine.

thanks Received on Fri Jul 23 2004 - 09:58:41 CDT