{"id":378,"date":"2024-12-19T02:07:22","date_gmt":"2024-12-19T02:07:22","guid":{"rendered":"https:\/\/w3buddy.com\/?p=378"},"modified":"2026-01-15T13:12:09","modified_gmt":"2026-01-15T07:42:09","slug":"identify-and-terminate-active-statistics-collection-job-in-database","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/identify-and-terminate-active-statistics-collection-job-in-database\/","title":{"rendered":"Identify and Terminate Active Statistics Collection Job in Database"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Learn how to handle statistics collection jobs that remain active in your Oracle database. This guide provides step-by-step instructions for identifying active sessions, verifying job statuses, and safely terminating lingering sessions to optimize database performance.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Identify Active Sessions in v$session<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Run the following query on both nodes to find sessions related to database statistics jobs:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT SID, SERIAL#, USERNAME, STATUS, PROCESS  \nFROM V$SESSION  \nWHERE MODULE LIKE '%DBMS%';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Note<\/em>: If no results are returned, repeat the query on the other node.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">2. Check Job Status in Scheduler<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Verify the status of the statistics collection job in the DBA_SCHEDULER_JOBS view:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE, COMMENTS  \nFROM DBA_SCHEDULER_JOBS  \nWHERE PROGRAM_NAME = 'GATHER_STATS_PROG';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. Cross-Check with OS Process<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Using the process ID from the session, monitor processes at the operating system level:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-bash\">[W3Buddy@&lt;hostname> ~]$ top -c -M<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">4. Terminate Sessions in the Database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Kill the identified sessions using their <code>SID<\/code> and <code>SERIAL#<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ALTER SYSTEM KILL SESSION '405,61711';  \nALTER SYSTEM KILL SESSION '10548,14473';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Note<\/em>: A warning may indicate that the session is marked for termination and will end after completing ongoing operations.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">5. Force Kill Process at OS Level<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If the session does not terminate, forcefully kill the associated OS process:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-bash\">[W3Buddy@&lt;hostname> ~]$ kill -9 5226<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">6. Verify Session Clearance<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Re-check active sessions in the database:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT SID, SERIAL#, USERNAME, STATUS, PROCESS  \nFROM V$SESSION  \nWHERE MODULE LIKE '%DBMS%';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Expected output: <code>no rows selected<\/code><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">7. Confirm Job State<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Ensure the job is no longer running:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE, COMMENTS  \nFROM DBA_SCHEDULER_JOBS  \nWHERE PROGRAM_NAME = 'GATHER_STATS_PROG';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Expected output: <code>no rows selected<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By following these steps, you can efficiently resolve issues with active statistics collection jobs in the database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to handle statistics collection jobs that remain active in your Oracle database. This guide provides step-by-step instructions for identifying active sessions, verifying job statuses, and safely terminating lingering sessions to optimize database performance. 1. Identify Active Sessions in v$session Run the following query on both nodes to find sessions related to database statistics [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-378","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/378","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=378"}],"version-history":[{"count":3,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/378\/revisions"}],"predecessor-version":[{"id":1502,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/378\/revisions\/1502"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=378"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=378"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=378"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}