Skip to Main Content
  • Questions

  • Waiting for parallel query to finish

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, atif.

Asked: August 03, 2016 - 12:41 pm UTC

Last updated: August 04, 2016 - 7:40 am UTC

Version: 10.2

Viewed 1000+ times

You Asked

Is there any way I could know the status of a parallel query i.e. the query has finished the job.
I am trying to use few parallel updates and inserts in a VB6 application and finally a truncate.
The truncate query causes "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".
I have observed that my second last parallel insert query returns immediately (which shouldn't because there are thousands of records) and truncate gets fired causing the problem.

Is there any way I could know the status of a parallel query or wait for its completion?

and Chris said...

If your application issues the truncate, then the parallel update before it has completed.

You're getting the ORA-54 because someone else has an uncommited transaction open against your table.

So you need to look at other sessions in your database and see what they're doing. If your session is not actively blocked, you may not be able to find this though:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523503800346688981

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

atif, August 04, 2016 - 4:15 am UTC

Did you mean that there is no way to know the PARALLEL query's status?
Connor McDonald
August 04, 2016 - 7:40 am UTC

The session that started the query will wait until all of the slaves are done, so its like any other query, ie, you get control back when its done.

You can also look at v$px_session to see what the slaves are doing.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.