ORA-0054

[jarthi]

Hi:
Please let me know , how to recover from this error.
We are trying to update a table and it is hanging.
When I tried to analyze with validate structure option, I got ORA-00054 error.
That means this table is locked.
How to unlock it?

Thanks,
Arthi

 

[karluk]

Clearly you will either have to wait for the update to finish or kill it and rollback. V$LOCK should show you which process is holding the lock.

 

[jarthi]

Hi Karluk:
Thanks for the quick response.
I am trying to kill the sessions one by one. It killed one. but hanging on the other.
I am using the following:
I logged in through svrmgrl/ connect internal

alter system kill session '8,32314';

I got the sid,serial# from v$session.
Is this right?

Thanks,
Arthi

 

[karluk]

Yes, I'm fairly sure that you are using the right columns in the kill. I'm afraid the rollback is taking a long time. I don't think there's anything you can do but wait for it to finish.

 

[jarthi]

If we restart the database, will all the processes be killed and the locks removed?

Thanks,
Arthi

 

[karluk]

I would advise against doing that. You would probably have to do a shutdown abort to kill a running process. More importantly, when you restart the database it will just resume the rollback (assuming, of course, that it's rollback that's causing the process to hang.)

 

[jarthi]

Hi Karluk:
I have 20 odd processes to kill, it didsn't even complete one process.
So I was desperate. I gave a 'shutdown immediate'.
'shutdown immediate' is also taking a long time and still not completed.
Hopefully it will finish.
Then I will start the killing process again, if the processes are still there.
Otherwise I don't know what to do.
Thanks for your help!

Arthi

 

[karluk]

I found this tip on Metalink to estimate how long the rollback will take.

Execute the command:

select used_ublk from v$transaction where ADDR=<value from
TADDR in v$session;

Note the value and wait a specific amount of time. Then repeat the select. Note the difference in used_ublk. This is the number of blocks rolled back between the two selects. From this you can calculate the rate at which blocks are being rolled back and estimate how long it will take the remaining blocks to rollback.

 

[karluk]

Please don't kill every single process you see in v$session. You should try to identify which one in particular is hanging.