Home » SQL & PL/SQL » SQL & PL/SQL » Query Hangs
Query Hangs [message #265733] Fri, 07 September 2007 03:43 Go to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
Hi All,

I try to run the below query but it hangs and no response, can you kindly suggest me a solution for it?

DELETE FROM METRO_FDC.T_L_PROC
WHERE (LOT_ID, STEP_M) IN (SELECT LOT_ID, STEP_M FROM TEMP_L_PROC);

The TEMP_L_PROC is a temporary table into which I have inserted data which needs to be queried and following it the deletion needs to be done in METRO_FDC.T_L_PROC.

Thanks
Re: Query Hangs [message #265736 is a reply to message #265733] Fri, 07 September 2007 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not hang, it may just take time.

Post execution plan, indexes, statistics and so on.

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).


Regards
Michel
Re: Query Hangs [message #265739 is a reply to message #265733] Fri, 07 September 2007 04:02 Go to previous messageGo to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
Thanks Michael. Below is the code

DELETE FROM METRO_FDC.T_L_PROC
WHERE (LOT_ID, STEP_M) IN (SELECT LOT_ID, STEP_M FROM TEMP_L_PROC);


Infact there is only 6 rows. I'm using Oracle 9i (9.2.0.7.0)
All permissions are granted to this user. I wonder how to figure this? As after few minutes SQLPLUS (not responding) hangs. I need to manually kill the session.

Re: Query Hangs [message #265744 is a reply to message #265733] Fri, 07 September 2007 04:22 Go to previous messageGo to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
DELETE FROM METRO_FDC.T_L_PROC 
WHERE (LOT_ID, STEP_M) IN (SELECT LOT_ID, STEP_M FROM TEMP_L_PROC);


The above code is run on Oracle9i (9.2.0.7) and it seems to hang until I have to manually kill the session. There is only 6 rows at the moment. Pls address if this query needs to be improved or rewritten? Thanks
Re: Query Hangs [message #265754 is a reply to message #265733] Fri, 07 September 2007 04:58 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF
Re: Query Hangs [message #265755 is a reply to message #265744] Fri, 07 September 2007 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does another session hold records to be deleted? Perhaps your DELETE waits for commit/rollback?

[Updated on: Fri, 07 September 2007 05:01]

Report message to a moderator

Re: Query Hangs [message #265773 is a reply to message #265744] Fri, 07 September 2007 06:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
raj75 wrote on Fri, 07 September 2007 11:22
DELETE FROM METRO_FDC.T_L_PROC 
WHERE (LOT_ID, STEP_M) IN (SELECT LOT_ID, STEP_M FROM TEMP_L_PROC);


The above code is run on Oracle9i (9.2.0.7) and it seems to hang until I have to manually kill the session. There is only 6 rows at the moment. Pls address if this query needs to be improved or rewritten? Thanks

"There is only 6 rows at the moment"

Does that mean there used to be 6 zillion? Do you mean that only 6 rows comply to your where clause? Are there 6 rows in metro_fdc.t_l_proc or in temp_l_proc?
Re: Query Hangs [message #265775 is a reply to message #265733] Fri, 07 September 2007 06:17 Go to previous messageGo to next message
raj75
Messages: 11
Registered: August 2007
Junior Member
Thanks All. The issue was related to database admin by the dba at the point of deletion. Commit succeeded.
Re: Query Hangs [message #265785 is a reply to message #265775] Fri, 07 September 2007 07:00 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
raj75
The issue was related to database admin by the dba at the point of deletion.

What does it mean? ./fa/1600/0/
Previous Topic: Table is mutating, trigger/function may not see it
Next Topic: Number of rows & size of tables of a tablespace
Goto Forum:
  


Current Time: Fri Dec 02 17:02:05 CST 2016

Total time taken to generate the page: 0.38500 seconds