Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Locks on a table related to HW (enq) waits

orausernDec 28 2010 — edited Dec 28 2010
Hi,

I have a production db in which locks are happening. The inserts to this table are hanging. This is due to High water (HW) enq waits for the table. The attempt to allocate extent to this table or to move the table to a different tablespace are giving following error:
--just replaced the name of the table in Prod with EMP in the sql below
SQL>  alter table emp move tablespace index1;
 alter table emp move tablespace index1
             *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL>  alter table emp allocate extent;
 alter table emp allocate extent
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
In this scenario, what are the choices available? Must I kill the session which is blocking and than try to allocate extent?

I will be thankful for any suggestions on this.

Regards,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 25 2011
Added on Dec 28 2010
2 comments
425 views