| ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #518423] |
Wed, 03 August 2011 16:24  |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
Hi all,
I have a procedure which disables the indexes, load the data, rebuild the indexes. When it reaches the point where it starts rebuilding the indexes, it gives this ORA-54 error. So I have tried adding the following:
EXECUTE IMMEDIATE 'commit';
EXECUTE IMMEDIATE 'alter session set ddl_lock_timeout=240' ;
EXECUTE IMMEDIATE 'ALTER INDEX WCLAIM.WC_FRDCLAIM_COMMODITY_FK REBUILD NOLOGGING';
But with ddl_lock_timeout=300 it sometimes throws the ORA-54 error and sometimes it gives "Internet Explorer cannot display the page error, which I assume is due to time-out.
I have also tried ONLINE option EXECUTE IMMEDIATE 'ALTER INDEX WCLAIM.WC_FRDCLAIM_COMMODITY_FK REBUILD ONLINE NOLOGGING'
But i got Insufficient privilege error. It needs CREATE TABLE rights directly to the user, and not through the role, which I dont have it.
I am using IE 7, which has time-out limit of 60 mins, according to Microsoft Support page, but my procedure takes max 10 mins, sometimes just 5 minutes.. so why does the IE displays that error. And if I look at the log file, its perfectly ok. So the procedure is doing what its supposed to do in background. But how can we fix the error on IE ?
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #518538 is a reply to message #518425] |
Thu, 04 August 2011 08:38  |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
Quote:> EXECUTE IMMEDIATE 'alter session set ddl_lock_timeout=240' ;
> EXECUTE IMMEDIATE 'ALTER INDEX WCLAIM.WC_FRDCLAIM_COMMODITY_FK REBUILD NOLOGGING';
I am NOT convinced that same SESSION runs both commands above.
It very well could be that the ALTER SESSION has ZERO impact on the ALTER INDEX statement.
So what is the other option to set ddl_lock_timeout, as it has to be done after the data loading is done, and before rebuilding the indexes. I have timeout , because rebuilding indexes was taking a long time, because the table seemed to be locked.
Can i use DBMS_LOCK.sleep, before rebuilding the index? But have read that DBMS_LOCK.sleep is not good if your wait time is more than 10 mins.- is this true ?
Thank you
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
