Oracle kill session procedure1/6/2023 ![]() ![]() NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sqlĬheck out what the offending session is doing: BREAK ON sid ON username ON osuser ON os_pid ON programĪND t.piece = 0 - optional to list just the first line SID LOCK_TYPE MODE_HELD MODE_REQUEST LOCK_ID1 KILL_SIDĢ18 Body Definition Lock Null None USER2.TEST_PACKAGE alter system kill session '218,12455' immediate 'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate ' kill_sidĪND UPPER(l.lock_id1) LIKE '%&package_name%' To get around this, you need to identify the locking session and kill it.Įxecuting this script as SYS (or another user with the appropriate privileges) will prompt you for the package name and reveal the culprit(s): BREAK ON sid ON lock_id1 ON kill_sid ORA-04021: timeout occurred while waiting to lock objectīasically, whilst someone or something else (a scheduled job perhaps?) is executing the package, then you won’t be able to perform the recompile. ![]() I’ve hit this problem a couple of times lately where a developer is trying to recompile a PL/SQL package and their session hangs until they eventually get this error: SQL> OR REPLACE PACKAGE BODY test_package
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |