Oracle Database Discussions

Hi guys,

there is a situation that I didnt understand.

I have table and want to create an index on it but when I try to create an index I got one of
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

or

ORA-01591: lock held by in-doubt distributed transaction 11.13.689049 errors.

as I know there is and .net application which has connection pooling and while this web services were active, I can not create index.

first thing that i did is querying V$LOCK table and i saw some TM locks from time to time and sometimes nothing. when there wasn't any locks on the table I try to create index, system wait for a while (1 minutes approximately) then I got "ORA-01591: lock held by in-doubt distributed ..." error again. (even "Application" wait event occured. in enterprise manager I saw a few red sql command which is waiting for my create index command)

after a quick search, I use PENDING_TRANS$ and PENDING_SESSIONS$ tables to force rollback to the transaction which is indicated on ORA-01591 error but just after I rollback that transaction, a new one come up every time.

today I use something else. First I locked table manually in EXCLUSIVE mode (and success it). then I try to create index again, command worked for a while and I got ORA-01591 error again.

any idea why is this happining and how can i create my index ?