{"id":3835,"date":"2025-06-09T11:46:28","date_gmt":"2025-06-09T11:46:28","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=3835"},"modified":"2025-06-09T11:46:30","modified_gmt":"2025-06-09T11:46:30","slug":"oracle-dbms_scheduler-commands","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-dbms_scheduler-commands\/","title":{"rendered":"Oracle DBMS_SCHEDULER Commands"},"content":{"rendered":"\n

Oracle DBMS_SCHEDULER<\/strong> enables automation of routine tasks like report generation, cleanup, and backups. Below is a concise guide to creating, managing, and monitoring Scheduler jobs.<\/p>\n\n\n\n

Create Scheduler Objects<\/h2>\n\n\n\n
-- Create a Schedule\nBEGIN\n  DBMS_SCHEDULER.CREATE_SCHEDULE(\n    schedule_name     => 'DAILYREPORTGEN_SCHED',\n    start_date        => SYSTIMESTAMP,\n    repeat_interval   => 'FREQ=DAILY;BYHOUR=11;BYMINUTE=30',\n    comments          => 'Daily report generation'\n  );\nEND;\n\n-- Create a Program\nBEGIN\n  DBMS_SCHEDULER.CREATE_PROGRAM(\n    program_name       => 'DAILYREPORTGENERATION',\n    program_type       => 'STORED_PROCEDURE',\n    program_action     => 'DAILYJOB.REPORTGENPROC',\n    number_of_arguments => 0,\n    enabled            => TRUE\n  );\nEND;\n\n-- Create a Job\nBEGIN\n  DBMS_SCHEDULER.CREATE_JOB(\n    job_name       => 'DAILYREPORTGEN_RUN',\n    program_name   => 'DAILYREPORTGENERATION',\n    schedule_name  => 'DAILYREPORTGEN_SCHED',\n    enabled        => FALSE\n  );\nEND;\n\n-- Create Job (Direct)\nBEGIN\n  DBMS_SCHEDULER.CREATE_JOB(\n    job_name        => 'MY_SCHEMA.MV_REF_REPORT_DATA',\n    job_type        => 'PLSQL_BLOCK',\n    job_action      => 'dbms_refresh.refresh(''\"MY_SCHEMA\".\"STC_NEXT_REPORT_MV_DATA\"'');',\n    repeat_interval => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=00',\n    enabled         => FALSE,\n    auto_drop       => FALSE\n  );\n  DBMS_SCHEDULER.enable(name => 'MY_SCHEMA.MV_REF_REPORT_DATA');\nEND;<\/code><\/pre>\n\n\n\n
Manage Scheduler Jobs<\/h2>\n\n\n\n
EXEC DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYREPORT');      -- Enable a job\nEXEC DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYREPORT');     -- Disable a job\nEXEC DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYREPORT');    -- Stop a job\nEXEC DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYREPORT');     -- Run job immediately\nEXEC DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYREPORT');    -- Drop a job<\/code><\/pre>\n\n\n\n
View Schedules and Jobs<\/h2>\n\n\n\n
-- View schedule details\nSELECT schedule_name, schedule_type, start_date, repeat_interval\nFROM dba_scheduler_schedules;\n\n-- Monitor running jobs\nSELECT job_name, session_id, running_instance, elapsed_time\nFROM dba_scheduler_running_jobs;\n\n-- Job logs\nSELECT * FROM DBA_SCHEDULER_JOB_LOG;\nSELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS;<\/code><\/pre>\n\n\n\n
Miscellaneous<\/h2>\n\n\n\n
-- Get DDL of a procedure\nSELECT dbms_metadata.get_ddl('PROCEDURE', 'DUP_ACC', 'SCOTT') FROM dual;\n\n-- Copy job from one user to another\nEXEC DBMS_SCHEDULER.COPY_JOB('SCOTT.MY_JOB_2','W3BUDDY.MY_JOB_2');\n\n-- Drop a schedule\nBEGIN\n  DBMS_SCHEDULER.DROP_SCHEDULE(schedule_name => 'DAILYREPORTGEN_SCHED', force => TRUE);\nEND;<\/code><\/pre>\n\n\n\n
External Script Job (with Credential)<\/h2>\n\n\n\n
-- Create credential\nBEGIN\n  dbms_credential.create_credential (\n    CREDENTIAL_NAME => 'MY_OS_USER',\n    USERNAME => 'oracle',\n    PASSWORD => 'oracle@123',\n    ENABLED => TRUE\n  );\nEND;\n\n-- Create external script job\nBEGIN\n  DBMS_SCHEDULER.CREATE_JOB(\n    job_name        => 'my_script_task',\n    job_type        => 'external_script',\n    job_action      => '\/export\/home\/oracle\/test.sh',\n    credential_name => 'MY_OS_USER',\n    start_date      => SYSDATE,\n    repeat_interval => 'FREQ=MINUTELY;BYMINUTE=1',\n    enabled         => TRUE,\n    auto_drop       => FALSE\n  );\nEND;<\/code><\/pre>\n\n\n\n
Log History & Audit<\/h2>\n\n\n\n
-- Job execution logs\nSELECT job_name, log_date, status, operation, additional_info\nFROM dba_scheduler_job_log\nORDER BY log_date DESC;\n\n-- Job run history\nSELECT job_name, status, actual_start_date, run_duration\nFROM DBA_SCHEDULER_JOB_RUN_DETAILS\nORDER BY actual_start_date DESC;<\/code><\/pre>\n\n\n\n
Auto Task Jobs<\/h2>\n\n\n\n
-- View auto tasks\nSELECT client_name, status, attributes, service_name\nFROM dba_autotask_client;\n\n-- Disable auto task\nBEGIN\n  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor');\nEND;<\/code><\/pre>\n\n\n\n
Manage Credentials<\/h2>\n\n\n\n
-- Create credential\nBEGIN\n  dbms_credential.create_credential (\n    CREDENTIAL_NAME => 'ORACLEOSUSER',\n    USERNAME => 'oracle',\n    PASSWORD => 'oracle@123',\n    ENABLED => TRUE\n  );\nEND;\n\n-- Drop credential\nEXEC DBMS_SCHEDULER.DROP_CREDENTIAL('ORACLEOSUSER');\n\n-- Update credential password\nEXEC DBMS_SCHEDULER.SET_ATTRIBUTE(\n  name => 'ORACLEOSUSER',\n  attribute => 'password',\n  value => 'oracle'\n);<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Oracle DBMS_SCHEDULER enables automation of routine tasks like report generation, cleanup, and backups. Below is a concise guide to creating, managing, and monitoring Scheduler jobs. Create Scheduler Objects Manage Scheduler Jobs View Schedules and Jobs Miscellaneous External Script Job (with Credential) Log History & Audit Auto Task Jobs Manage Credentials<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-3835","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}