1/9/2024 0 Comments Em client user process![]() The explanation was that doing kill -9 prioritized the rollback/cleanup operation as #1 on its to do list, everything else came later on that list. On the other hand, kill -9 essentially pulls the rug out from under the Oracle session by killing the shadow process, forcing PMON to dedicate all of its might to rolling back as quickly as possible, not just when it has the time to do so. ![]() As observed earlier, sometimes sessions would be marked as killed for a very long time. However, this can take a long time depending on how much rolling back and how many other demands are being made on pmon(on a busy box, it could be a lot). ORA-04021: timeout occurred while waiting to lock object SELECT SYSDATE FROM DUAL Įlapsed: alter package p_dataset compile Įlapsed: way it was explained to me years ago is that when you do and alter system kill session 'nnn,nnn' then pmon goes to work cleaning up that session, rolling back uncommitted work, etc. SERNAME STATUS MODULE OSUSER CLIENT_INFO LOGED_ONĢ1,10778 INACTIVE T.O.A.D. Is there any way to recompile SELECT sid||','||serial# username,Ħ TO_CHAR(logon_time,'DD-MON-YY HH:MI:SSSS') loged_on Is there any way without bouncing db to over come this? I can compile all other objects except this one which got hung-up. I was debugging a package and session got hung-up and finally I had to kill the session and close the application - TOAD where I was debugging.Īnd now its over 24 hours and still can not compile that object from TOAD, PL/SQL Developer or SqlPlus and after 5 minutes error comes 'ORA-04021: timeout occurred while waiting to lock object '. kill -9 is not something I recommend, especially if you use the above technique to see it rolling back. We might be doing on demand recovery of the blocks which makes it "appear" faster but we still have to recover the transaction. If you kill -9, it might appear that the session is gone "faster" but not really. Now we are done rolling back select username, status from v$session where username = 'BIG_TABLE' Session is still select used_ublk from v$transaction We can watch the used_ublk shrink and even use that to estimate when it'll be /ġ row select username, status from v$session where username = 'BIG_TABLE' ORA-00031: session marked for select username, status from v$session where username = 'BIG_TABLE' ġ row select used_ublk from v$transaction That means the server heard us and is in the process of rolling back but it'll take a alter system kill session '8,1082' I kill it and get the message "it is marked for kill". ![]() It generated that much undo so SID_SERIAL STATUS MODULE ACTIONīIG_TABLE(8,1082) ospid = 13903 command = 7 program = (TNS V1-V3) dedicated server=13904 This is what you'll see (you need to be more selective on the query against v$transaction of course, I just had one active DML session select used_ublk from v$transaction In a schema "big_table" i started a delete against a 1,000,000 row indexed table. You can monitor how the killed session is doing as far as rolling back is concerned in v$transaction via the used_ublk column.Ĭonsider this example. If the job took an hour to get where it is, it might take even LONGER to "undo" what its done. The job isn't "going on" it is actually "going back" - it is rolling back.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |