oracle 19c 升级job 没有同步的解决办法
2022/7/13 2:20:26
本文主要是介绍oracle 19c 升级job 没有同步的解决办法,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
########sample 2
APPLIES TO:
Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
Statspack schema import in 19C failing with following error:
IMP-00017: following statement failed with ORACLE error 27486
"BEGIN DBMS_JOB.ISUBMIT(JOB=>1,WHAT=>'statspack.snap;',NEXT_DATE=>TO_DATE("
"'2020-07-26:07:10:00','YYYY-MM-DD:HH24:MI:SS'),INTERVAL=>'TRUNC(SYSDATE+30/"
"1440,''MI'')',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 27486 encountered
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9396
CHANGES
No changes
CAUSE
Missing privilege on DBMS_JOB.
SOLUTION
In 19c Privilege on DBMS_JOB need to be explicitly granted to the importing user:
Grant Create Job To "<IMPORTING SCHEMA>
#####sample 1
IF: An Example to Convert from DBMS_JOB Jobs to DBMS_SCHEDULER Jobs (Doc ID 2117140.1) To BottomTo Bottom
In this Document
Goal
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
GOAL
This document summarizes the steps to convert a job created using DBMS_JOB to a DBMS_SCHEDULER job with the help of an example.
SOLUTION
1. Obtain the DDL for DBMS_JOB job.
The definition of a job submitted via DBMS_JOB can be obtained by using the dbms_job.user_export procedure.
set serveroutput on
DECLARE
callstr VARCHAR2(500);
BEGIN
dbms_job.user_export(23, callstr);
dbms_output.put_line(callstr);
END;
/
dbms_job.isubmit(job=>23,what=>'sample_procedure;',next_date=>to_date('2016-03-1
6:17:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'SYSDATE + 1',no_parse=>TRUE);
Looking at the DDL, this job executes the stored procedure sample_procedure at 5 PM every day. This can be confirmed from the output of dba_jobs as well.
SQL> select log_user, schema_user, job,next_date,what,interval from dba_jobs where log_user='TEST';
LOG_USER SCHEMA_USE JOB NEXT_DATE WHAT INTERVAL
---------- ---------- ---------- -------------------- ------------------------------ ------------------------------
TEST TEST 23 16-MAR-16 sample_procedure; SYSDATE + 1
2. Create a DBMS_SCHEDULER job similar to above DBMS_JOB
A scheduler job has to be created such that it satisfies all the conditions of the DBMS_JOB job. In this example the job should execute the stored procedure sample_procedure at 5 PM every day.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'sample_procedure_job', -- provide a name for the job
job_type => 'STORED_PROCEDURE', -- job executes a stored procedure
job_action => 'sample_procedure',
start_date => TRUNC(SYSDATE) + 17/24, -- start today at 5 PM
repeat_interval => 'freq=daily; byhour=17; byminute=0', -- repeat at 5 PM everyday
end_date => NULL,
enabled => TRUE, -- job is enabled
comments => 'Job created using the CREATE JOB procedure.');
End;
/
3. Ensure that the scheduler job is created as per the requirements
select JOB_NAME,JOB_TYPE,JOB_ACTION,STATE,NEXT_RUN_DATE, REPEAT_INTERVAL from dba_scheduler_jobs where job_name='SAMPLE_PROCEDURE_JOB';
JOB_NAME JOB_TYPE JOB_ACTION ENABL STATE NEXT_RUN_DATE REPEAT_INTERVAL
-------------------- ---------------- -------------------- ----- ------------ ---------------------------------------- ----------------------------------------
SAMPLE_PROCEDURE_JOB STORED_PROCEDURE sample_procedure TRUE SCHEDULED 16-MAR-16 05.00.00.000000 PM +00:00 freq=daily; byhour=17; byminute=0
4. Drop the DBMS_JOB job
exec dbms_job.remove(23);
REFERENCES
NOTE:270256.1 - How to Create a Job Using DBMS_SCHEDULER - 10g Job Scheduling Feature
NOTE:2109399.1 - How to Schedule a Job using DBMS_JOB
这篇关于oracle 19c 升级job 没有同步的解决办法的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议,共探开源教育未来
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升