{"id":18532,"date":"2026-04-15T10:56:49","date_gmt":"2026-04-15T09:56:49","guid":{"rendered":"https:\/\/roglacup.com\/klaus62\/?p=18532"},"modified":"2026-04-16T07:22:30","modified_gmt":"2026-04-16T06:22:30","slug":"dba_scheduler_jobs","status":"publish","type":"post","link":"https:\/\/roglacup.com\/klaus62\/2026\/04\/15\/dba_scheduler_jobs\/","title":{"rendered":"DBA_SCHEDULER_JOBS"},"content":{"rendered":"\n<p><strong>Difference in DBA_SCHEDULAR_JOBS &#8211; Primary\/Standby<\/strong><\/p>\n\n\n\n<p>When the application schemas are compared from Primary and Standby we&nbsp;see a difference in sequence cache value and&nbsp;<code>dba_scheduler_jobs<\/code>. Here sequence values seems to be due to an Archive gap but&nbsp;<code>dba_schedular_job<\/code>&nbsp;is the one which is a real concern to us. This article explains why there are no records in standby and records in production .<\/p>\n\n\n\n<p><strong>Explanation<\/strong><\/p>\n\n\n\n<p>We did testing and found that there are 3 main tables for Schedules:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><code>DBA_SCHEDULER_JOB_RUN_DETAILS<\/code>: Displays log run details for all Scheduler jobs in the database.<\/li>\n\n\n\n<li><code>DBA_SCHEDULER_JOBS<\/code>: It displays information about all Scheduler jobs in the database.<\/li>\n\n\n\n<li><code>DBA_SCHEDULER_JOB_LOG<\/code>: It displays log information for all Scheduler jobs in the database.<\/li>\n<\/ol>\n\n\n\n<p><code>DBA_SCHEDULER_JOBS<\/code>&nbsp;only shows information about all Scheduler jobs in the database. Interestingly a scheduled job can exist on either a primary database or a logical standby database, but a scheduled job cannot exist on a physical standby database because it would then be possible for a scheduled task to run on both the primary and its physical standby counterpart. However, for a physical standby database, any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes.<\/p>\n\n\n\n<p>You can look at the following tables and compare records in Standby are updated with Primary:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>DBA_SCHEDULER_JOB_RUN_DETAILS<\/code><\/li>\n\n\n\n<li><code>DBA_SCHEDULER_JOB_LOG<\/code><\/li>\n<\/ul>\n\n\n\n<p><code>DBMS_SCHEDULER<\/code>&nbsp;uses the&nbsp;<code>DATABASE_ROLE<\/code>&nbsp;attribute to determine whether it should run on either:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>(a) The primary database.<\/li>\n<\/ul>\n\n\n\n<p>or<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>(b) A specified logical standby database.<\/li>\n<\/ul>\n\n\n\n<p>You can use the&nbsp;<code>DBMS_SCHEDULER.SET_ATTRIBUTE<\/code>&nbsp;procedure to set the&nbsp;<code>database_role<\/code>. During a switchover or failover, the new role of the database is recognized and the appropriate jobs will be run based on the new role.<\/p>\n\n\n\n<p>So Ideally the&nbsp;<code>dba_scheduler_jobs<\/code>&nbsp;would have no data for Standby Database<\/p>\n\n\n\n<p>Posted from:<a href=\"https:\/\/support.dbvisit.com\/hc\/en-us\/articles\/216506768-Difference-in-DBA-SCHEDULAR-JOBS-Primary-Standby\">https:\/\/support.dbvisit.com\/hc\/en-us\/articles\/216506768-Difference-in-DBA-SCHEDULAR-JOBS-Primary-Standby<\/a><\/p>\n\n\n\n<p><strong>dbms_scheduler log history &#8211; purging manually<\/strong><\/p>\n\n\n\n<p>Today, I came across a SYSAUX tablespace that was asking for more space. It was about 12Gb, which seems a bit large to me. If you use the&nbsp;$ORACLE_HOME\/rdbms\/admin\/awrinfo.sql&nbsp;, you will get a report of the occupants. &nbsp;Occupant JOB_SCHEDULER took over 11Gb space, which makes you think.<\/p>\n\n\n\n<p>The default out-of-the-box maintainance job, uses the global attribute LOG_HISTORY to remove old dbms_scheduler job logging:<\/p>\n\n\n\n<p>select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;<\/p>\n\n\n\n<p>Now this is default 30 days, and can be set with:<\/p>\n\n\n\n<p>exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(&#8216;log_history&#8217;,&#8217;15&#8217;);<\/p>\n\n\n\n<p>But what if your maintainance jobs are failing for some reason (timeout\/bugs?). You may need to clean it manually. The easy way to do this, is to use:<\/p>\n\n\n\n<p>exec DBMS_SCHEDULER.PURGE_LOG(&lt;days&gt;, which_log=&gt;&#8217;JOB_LOG&#8217;);<\/p>\n\n\n\n<p>so for example:<\/p>\n\n\n\n<p>exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=&gt;&#8217;JOB_LOG&#8217;);<\/p>\n\n\n\n<p>But if the number of logs is very large, you have to do this carefully, step by step:<\/p>\n\n\n\n<p>First determine the distribution of logging you have.<\/p>\n\n\n\n<p>Select count(1) from dba_scheduler_job_log where log_date &lt; sysdate \u2013 100;<\/p>\n\n\n\n<p>Make sure that you get an idea like:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>older than 300 days<\/td><td>12 rows<\/td><\/tr><tr><td>older than 250 days<\/td><td>12831 rows<\/td><\/tr><tr><td>older than 200 days<\/td><td>438121 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Posted from: <a href=\"https:\/\/webgeest.blogspot.com\/2015\/06\/purge-loghistory.html\">https:\/\/webgeest.blogspot.com\/2015\/06\/purge-loghistory.html<\/a><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Oracle Scheduler SQL Cheat Sheet<\/h1>\n\n\n\n<p>&nbsp;Oracle Scheduler is a built-in job scheduler in Oracle Database that enables you to manage and execute various tasks, such as running database program units, external executables, and scripts. It provides a flexible and sophisticated way to schedule jobs based on time, events, or dependencies, allowing you to automate routine tasks and reduce manual intervention.<a><\/a><\/p>\n\n\n\n<p>The Scheduler offers advanced features, including prioritization of jobs based on business requirements, resource allocation, and monitoring of job execution. It also supports execution of jobs in a clustered environment, such as Oracle Real Application Clusters (Oracle RAC). With Oracle Scheduler, you can streamline your database operations, improve reliability, and reduce operating costs.<\/p>\n\n\n\n<p>You can use following SQLs to manage your Scheduler and the jobs:<\/p>\n\n\n\n<p>Check Scheduler Job Details in CDB<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; View job details in the Container Database (CDB)<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>&nbsp; CON_ID,<\/p>\n\n\n\n<p>&nbsp; JOB_NAME,<\/p>\n\n\n\n<p>&nbsp; JOB_TYPE,<\/p>\n\n\n\n<p>ENABLED,<\/p>\n\n\n\n<p>STATE,<\/p>\n\n\n\n<p>&nbsp; NEXT_RUN_DATE,<\/p>\n\n\n\n<p>&nbsp; REPEAT_INTERVAL<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; cdb_scheduler_jobs;<\/p>\n\n\n\n<p>Monitor Currently Running Jobs<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; View currently running jobs<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>&nbsp; job_name,<\/p>\n\n\n\n<p>&nbsp; session_id,<\/p>\n\n\n\n<p>&nbsp; running_instance,<\/p>\n\n\n\n<p>&nbsp; elapsed_time<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; dba_scheduler_running_jobs;<\/p>\n\n\n\n<p>View Job Run Details<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; View details of job runs<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>*<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; DBA_SCHEDULER_JOB_RUN_DETAILS;<\/p>\n\n\n\n<p>View Job-Related Logs<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; View logs related to job execution<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>*<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; DBA_SCHEDULER_JOB_LOG;<\/p>\n\n\n\n<p>Check All Scheduler Schedules<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; Set formatting options for output<\/p>\n\n\n\n<p>SET PAGESIZE 200<\/p>\n\n\n\n<p>SET LINES 299<\/p>\n\n\n\n<p>COL START_DATEFOR A45<\/p>\n\n\n\n<p>COL REPEAT_INTERVAL FOR A45<\/p>\n\n\n\n<p>COL schedule_name FOR A34<\/p>\n\n\n\n<p>&#8212; View all scheduler schedules<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>&nbsp; schedule_name,<\/p>\n\n\n\n<p>&nbsp; schedule_type,<\/p>\n\n\n\n<p>start_date,<\/p>\n\n\n\n<p>&nbsp; repeat_interval<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; dba_scheduler_schedules;<\/p>\n\n\n\n<p>History of All Scheduler Job Runs<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; Set formatting options for output<\/p>\n\n\n\n<p>SET PAGESIZE 299<\/p>\n\n\n\n<p>SET LINES 299<\/p>\n\n\n\n<p>COL JOB_NAME FOR A24<\/p>\n\n\n\n<p>COL actual_start_date FOR A56<\/p>\n\n\n\n<p>COL RUN_DURATION FOR A34<\/p>\n\n\n\n<p>&#8212; View history of all scheduler job runs<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>&nbsp; job_name,<\/p>\n\n\n\n<p>status,<\/p>\n\n\n\n<p>&nbsp; actual_start_date,<\/p>\n\n\n\n<p>&nbsp; run_duration<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; DBA_SCHEDULER_JOB_RUN_DETAILS<\/p>\n\n\n\n<p>ORDER BY<\/p>\n\n\n\n<p>&nbsp; ACTUAL_START_DATE DESC;<\/p>\n\n\n\n<p>CheckLog Information for All Scheduler Jobs<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; Set formatting options for output<\/p>\n\n\n\n<p>SET PAGESIZE 299<\/p>\n\n\n\n<p>SET LINES 299<\/p>\n\n\n\n<p>COL job_name FOR A24<\/p>\n\n\n\n<p>COL log_date FOR A40<\/p>\n\n\n\n<p>COL operation FOR A19<\/p>\n\n\n\n<p>COL additional_info A79<\/p>\n\n\n\n<p>&#8212; View log information for all scheduler jobs<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>&nbsp; job_name,<\/p>\n\n\n\n<p>&nbsp; log_date,<\/p>\n\n\n\n<p>status,<\/p>\n\n\n\n<p>&nbsp; OPERATION,<\/p>\n\n\n\n<p>&nbsp; ADDITIONAL_INFO<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; dba_scheduler_job_log<\/p>\n\n\n\n<p>ORDER BY<\/p>\n\n\n\n<p>&nbsp; log_date DESC;<\/p>\n\n\n\n<p>Check All Scheduler Windows Details<\/p>\n\n\n\n<p>SQL<\/p>\n\n\n\n<p>&#8212; Set formatting options for output<\/p>\n\n\n\n<p>SET PAGESIZE 300<\/p>\n\n\n\n<p>SET LINESIZE 200<\/p>\n\n\n\n<p>&#8212; View all scheduler windows details<\/p>\n\n\n\n<p>SELECT<\/p>\n\n\n\n<p>*<\/p>\n\n\n\n<p>FROM<\/p>\n\n\n\n<p>&nbsp; dba_scheduler_windows;<\/p>\n\n\n\n<p>Posted from: <a href=\"https:\/\/www.fahdmirza.com\/2025\/01\/oracle-scheduler-sql-cheat-sheet.html\">https:\/\/www.fahdmirza.com\/2025\/01\/oracle-scheduler-sql-cheat-sheet.html<\/a><\/p>\n\n\n\n<p><strong>OWNER vs JOB_CREATOR in DBMS_SCHEDULER<\/strong><\/p>\n\n\n\n<p>A few days ago I received an email from a subscriber. The question looked simple\u2026 but it came with a twist:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Good morning Javier, \nI have two database users: \n1. OWNER \n2. DBA \n\nDBA creates a job where the owner is user OWNER.\nThe job runs without issues.\nThe user DBA is dropped.\n\nTricky question:  What happens with this job?\n1) It runs? \n2) It Fails?\nWhy? \n<\/pre>\n\n\n\n<p>The \u201cautomatic\u201d answer many of us would give (me included) is: if the job \u201cbelongs to\u201d&nbsp;<strong>OWNER<\/strong>&nbsp;and&nbsp;<strong>OWNER<\/strong>&nbsp;still exists, then&nbsp;<strong>it should keep running<\/strong>.<\/p>\n\n\n\n<p>But Oracle Scheduler stores more information than we typically look at out of habit\u2026 and that\u2019s exactly where the surprises show up.<\/p>\n\n\n\n<h6 class=\"wp-block-heading\">Only checking the OWNER column: professional reflex<\/h6>\n\n\n\n<p>I\u2019m the first one guilty of this.<\/p>\n\n\n\n<p>I always go straight to&nbsp;<strong>OWNER<\/strong>&nbsp;to know who owns an object. A table might be owned by one user while its index ends up owned by SYSTEM (if a DBA created it, it can happen), or you might be checking who owns a DB link, a synonym, and so on.<\/p>\n\n\n\n<p>So&nbsp;<strong>OWNER<\/strong>&nbsp;becomes the default reference column. But in Scheduler there is another concept that often goes unnoticed: the user who created the job.<\/p>\n\n\n\n<p>When the creator and the owner are the same, there\u2019s usually no big problem. Even when jobs are created by SYS or SYSTEM, you can assume the creator will always exist. But here we care about a very specific scenario: when&nbsp;<strong>OWNER and JOB_CREATOR are different<\/strong>.<\/p>\n\n\n\n<p>This happens, for example, when a DBA (or a user with privileges such as&nbsp;<code>CREATE ANY JOB<\/code>) creates jobs \u201con behalf of\u201d another schema.<\/p>\n\n\n\n<h6 class=\"wp-block-heading\">What does the documentation say\u2026 and what does it omit?<\/h6>\n\n\n\n<p>Oracle\u2019s Scheduler documentation explains the model, privileges, and objects (jobs, programs, schedules, chains), and makes it clear there are multiple ways to manage jobs.<\/p>\n\n\n\n<p>But it usually does so from a \u201cfunctional\u201d point of view: it talks about creating jobs, enabling\/disabling them, monitoring them, etc. What it doesn\u2019t really cover are best-practice recommendations, nor does it warn you about collateral effects of&nbsp;<strong>offboarding<\/strong>&nbsp;(when a person\u2019s account is removed due to security policy).<\/p>\n\n\n\n<p>And that\u2019s exactly what I miss in the documentation: a note or warning that says something like:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u201cIf you run\u00a0<code>DROP USER<\/code>\u00a0on the creator, even if the owner still exists, the job becomes BROKEN.\u201d<\/strong><\/li>\n<\/ul>\n\n\n\n<h6 class=\"wp-block-heading\">AskTom and Oracle Support<\/h6>\n\n\n\n<p>Apparently, this didn\u2019t happen only to this subscriber, and it\u2019s not a theoretical paranoia. It has happened to more than one person and you can see it as a real case on AskTom (Chris Saxon): when the creator is removed (<code>DROP USER<\/code>), some jobs end up broken. And in the job log you can see an explicit reason:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>REASON=\"Job creator: &lt;usuario> dropped\"<\/code><\/li>\n<\/ul>\n\n\n\n<p>Reference (AskTom):<br><a href=\"https:\/\/asktom.oracle.com\/ords\/f?p=100:11:0::::P11_QUESTION_ID:9537363000346826584\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/asktom.oracle.com\/ords\/f?p=100:11:0::::P11_QUESTION_ID:9537363000346826584<\/a><\/p>\n\n\n\n<p>There is also a support note that corroborates this behavior (in the sense of \u201cthis can happen and there is an explanation\u201d). In my opinion, though, this is something we should be reading in the public documentation\u2014not discovering through support after jobs suddenly become broken while the OWNER itself has not changed.<\/p>\n\n\n\n<p>In other words, in many teams&nbsp;<strong>the problem is discovered when the job fails<\/strong>.<\/p>\n\n\n\n<p>And you need to be careful, because&nbsp;<strong>recreating the \u2018creator\u2019 user does not automatically fix the problem<\/strong>.<\/p>\n\n\n\n<p>Posted from:<a href=\"https:\/\/cafedatabase.com\/en\/owner-vs-job_creator-in-dbms_scheduler\/\">https:\/\/cafedatabase.com\/en\/owner-vs-job_creator-in-dbms_scheduler\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Difference in DBA_SCHEDULAR_JOBS &#8211; Primary\/Standby When the application schemas are compared from Primary and Standby we&nbsp;see a difference in sequence cache value and&nbsp;dba_scheduler_jobs. Here sequence values seems to be due to an Archive gap but&nbsp;dba_schedular_job&nbsp;is the one which is a real concern to us. This article explains why there are no records in standby and&hellip;&nbsp;<a href=\"https:\/\/roglacup.com\/klaus62\/2026\/04\/15\/dba_scheduler_jobs\/\" class=\"\" rel=\"bookmark\">Read More &raquo;<span class=\"screen-reader-text\">DBA_SCHEDULER_JOBS<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":18534,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[884,1047],"tags":[],"class_list":["post-18532","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-oracle-it-vendors"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/18532","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/comments?post=18532"}],"version-history":[{"count":1,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/18532\/revisions"}],"predecessor-version":[{"id":18533,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/18532\/revisions\/18533"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/media\/18534"}],"wp:attachment":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/media?parent=18532"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/categories?post=18532"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/tags?post=18532"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}