For many months I have had constant problems of clients calling me complaining of sessions marked KILLED<\/strong> <\/span>not disappearing, locked objects or jobs running forever. When analyzing the cause, I usually faced Oracle 9i or 10g versions with one waits below:<\/p>\n In addition to being caused by the difference in versions, this failure of communication between databases may also be associated with the Oracle bugs that need to be corrected, lapses in network and\/or failures in O.S.<\/p>\n Since it was not possible to align all Oracle versions of the company or solve the above problems, I developed the following job that searches the sessions that are “waiting for nothing” sporadically, remove them if they are in wait for more than X seconds, and finally kill the process on the operating system to ensure that it will be eliminated.<\/p>\n Warning:<\/strong> Killing an Oracle process on the operating system is never a task recommended by Oracle. Use it at your own risk. This procedure was tested on several databases running on “Dedicated Server Mode”, not on “Shared Server Mode”.<\/span><\/p>\n Steps: First of all, make sure that Java is enabled in your Oracle instance:<\/p>\n Let’s start creating the Java Source:<\/p>\n NOTE: If necessary, make the appropriate adjustments according to the location of your OS binaries<\/em><\/p>\n Create a procedure to do the interface with the user who created the Java Source.<\/p>\n Now grant the following privileges to the same user (change the script with the appropriate username).<\/p>\n To create this procedure, first grant the privileges below to the same user who owns the procedure in step 2.<\/p>\n Now create a procedure that will search sessions that are waiting DBLink for over 2 hours to eliminate them. After that, this same process will “kill” those sessions in OS.<\/p>\n Finally, create a job that will run every hour:<\/p>\n Great, now the “everlasting wait” and killed Oracle sessions will be automaticaly eliminated.<\/p>\n This post was based on Article http:\/\/www.oracle-base.com\/articles\/8i\/shell-commands-from-plsql.php<\/a>.<\/p>\nHave you enjoyed? Please leave a comment or give a \ud83d\udc4d!<\/b>\n\n
\n1- Create a java source that runs the “kill” command on the Operating System
\n2- Create a procedure that calls\u00a0this java source.
\n3- Create a procedure which will fetch the dead sessions and kill the processes.
\n4- Create a job to run the procedure on item every hour.<\/p>\n1) Java Source to kill the process:<\/h3>\n
SQL> select comp_name, version from dba_registry where comp_name like '%JAVA%';\r\n\r\nCOMP_NAME VERSION\r\n-------------------------------------------------------------------------------- ------------------------------\r\nJServer JAVA Virtual Machine 9.2.0.8.0\r\n\r\nSQL><\/pre>\n
CREATE AND COMPILE JAVA SOURCE NAMED \"Kill\" AS\r\nimport java.io.*;\r\npublic class Kill {\r\n public static void executeCommand(String spid) {\r\n try {\r\n String[] finalCommand;\r\n if (isWindows()) {\r\n finalCommand = new String[4];\r\n \/\/ Use the appropriate path for your windows version.\r\n \/\/finalCommand[0] = \"C:\\\\winnt\\\\system32\\\\cmd.exe\"; \/\/ Windows NT\/2000\r\n \/\/finalCommand[0] = \"C:\\\\windows\\\\syswow64\\\\cmd.exe\"; \/\/ Windows 64-bit\r\n finalCommand[0] = \"C:\\\\windows\\\\system32\\\\taskkill.exe\"; \/\/ Windows XP\/2003\r\n finalCommand[1] = \"\/f\";\r\n finalCommand[2] = \"\/pid\";\r\n finalCommand[3] = spid;\r\n }\r\n else {\r\n finalCommand = new String[3];\r\n finalCommand[0] = \"\/bin\/kill\";\r\n finalCommand[1] = \"-9\";\r\n finalCommand[2] = spid;\r\n }\r\n\r\n final Process pr = Runtime.getRuntime().exec(finalCommand);\r\n pr.waitFor();\r\n\r\n new Thread(new Runnable(){\r\n public void run() {\r\n BufferedReader br_in = null;\r\n try {\r\n br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));\r\n String buff = null;\r\n while ((buff = br_in.readLine()) != null) {\r\n System.out.println(\"Process out :\" + buff);\r\n try {Thread.sleep(100); } catch(Exception e) {}\r\n }\r\n br_in.close();\r\n }\r\n catch (IOException ioe) {\r\n System.out.println(\"Exception caught printing process output.\");\r\n ioe.printStackTrace();\r\n }\r\n finally {\r\n try {\r\n br_in.close();\r\n } catch (Exception ex) {}\r\n }\r\n }\r\n }).start();\r\n\r\n new Thread(new Runnable(){\r\n public void run() {\r\n BufferedReader br_err = null;\r\n try {\r\n br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));\r\n String buff = null;\r\n while ((buff = br_err.readLine()) != null) {\r\n System.out.println(\"Process err :\" + buff);\r\n try {Thread.sleep(100); } catch(Exception e) {}\r\n }\r\n br_err.close();\r\n }\r\n catch (IOException ioe) {\r\n System.out.println(\"Exception caught printing process error.\");\r\n ioe.printStackTrace();\r\n }\r\n finally {\r\n try {\r\n br_err.close();\r\n } catch (Exception ex) {}\r\n }\r\n }\r\n }).start();\r\n }\r\n catch (Exception ex) {\r\n System.out.println(ex.getLocalizedMessage());\r\n }\r\n }\r\n\r\n public static boolean isWindows() {\r\n if (System.getProperty(\"os.name\").toLowerCase().indexOf(\"windows\") != -1)\r\n return true;\r\n else\r\n return false;\r\n }\r\n\r\n};\r\n\/\r\n\r\nshow errors java source \"Kill\"<\/pre>\n2) Procedure to execute Java Source<\/h3>\n
CREATE PROCEDURE host_kill (p_spid IN VARCHAR2)\r\nAS LANGUAGE JAVA \r\nNAME 'Kill.executeCommand (java.lang.String)';\r\n\/<\/pre>\n
DECLARE\r\n l_schema VARCHAR2(30) := 'USERNAME' -- Adjust as required.\r\nBEGIN\r\n DBMS_JAVA.grant_permission(l_schema, 'SYS:java.io.FilePermission', '\/bin\/kill', 'execute' );\r\n DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');\r\n DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');\r\nEND;\r\n\/<\/pre>\n
3) Create procedure to kill the “everlasting wait” sessions<\/h3>\n
GRANT SELECT ON V_$SESSION TO USERNAME;\r\nGRANT SELECT ON V_$SESSION_WAIT TO USERNAME;\r\nGRANT SELECT ON V_$PROCESS TO USERNAME;<\/pre>\n
CREATE OR REPLACE PROCEDURE KILL_EXPIRED_DBLINK_CONNECTION AS\r\n SESSION_MARKED_KILL EXCEPTION;\r\n PRAGMA EXCEPTION_INIT(SESSION_MARKED_KILL, -31);\r\n -------------------\r\n CURSOR C1 IS\r\n SELECT 'alter system kill session ''' || A.SID || ',' || A.SERIAL# || ''' immediate' KILL\r\n FROM V$SESSION A,\r\n V$SESSION_WAIT B\r\n WHERE A.SID = B.SID\r\n AND B.EVENT LIKE 'SQL*Net % from dblink'\r\n AND B.STATE = 'WAITING'\r\n AND B.SECONDS_IN_WAIT > 7200; -- 2 hours\r\n -------------------\r\n CURSOR C2 IS\r\n SELECT A.SPID\r\n FROM V$PROCESS A,\r\n V$SESSION B\r\n WHERE A.ADDR = B.PADDR\r\n AND B.STATUS LIKE 'KILLED';\r\nBEGIN\r\n FOR I1 IN C1\r\n LOOP\r\n BEGIN\r\n EXECUTE IMMEDIATE I1.KILL;\r\n EXCEPTION\r\n WHEN SESSION_MARKED_KILL THEN\r\n NULL;\r\n END;\r\n END LOOP;\r\n -------------------\r\n FOR I2 IN C2\r\n LOOP\r\n HOST_KILL(P_SPID => I2.SPID);\r\n END LOOP;\r\nEND;\r\n\/<\/pre>\n
4) Create cleaning job<\/h3>\n
-- Oracle 9i\r\n\r\nDECLARE\r\n JOBN NUMBER;\r\nBEGIN\r\n SYS.DBMS_JOB.SUBMIT(JOB => JOBN, WHAT => 'BEGIN KILL_EXPIRED_DBLINK_CONNECTION; END;', NEXT_DATE => SYSDATE, INTERVAL => 'SYSDATE + 1 \/ 24');\r\n COMMIT;\r\nEND;\r\n\/\r\n\r\n-- Oracle >= 10g\r\nBEGIN\r\nDBMS_SCHEDULER.CREATE_JOB (\r\n job_name => 'JOB_KILL_EXPIRED_DBLINK_CONN',\r\n job_type => 'STORED_PROCEDURE',\r\n job_action => 'KILL_EXPIRED_DBLINK_CONNECTION',\r\n number_of_arguments => 0,\r\n start_date => SYSDATE,\r\n repeat_interval => 'FREQ=HOURLY;INTERVAL=1;BYMINUTE=0',\r\n end_date => NULL,\r\n job_class => 'DEFAULT_JOB_CLASS',\r\n enabled => TRUE,\r\n auto_drop => FALSE,\r\n comments => 'Job to kill expired DBLink''s connections');\r\nEND;\r\n\/<\/pre>\n
