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,