########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>
### 需要说明的oracle 19c cdb 级别和pdb 级别授权的区别， cdb 存系统的存储过程，pdb 级别存用户的存储过程
所以 在存储过程这一级别上授权 ,如果是在cdb需要加入container=all ：
比如 grant execute any procedure to user container=all
(在CDB级别中对用户进行授权,不带 container 子句的效果:仅作用于当前CDB 2. 在CDB级别中对用户进行授权,带 container=all 子句的效果:作用于当前CDB和所有)
另外系统对象的需要在cdb 级别授权
grant select on sys.obj$ to dbmonopr;
//授权同步账号查看数据库中的对象。sys.obj$表是Oracle字典表中的对象基础表，存放Oracle的所有对象。
grant select on SYS.COL$ to dbmonopr;
//授权同步账号查看数据库表中列的定义信息。SYS.COL$用于保存表中列的定义信息。
grant select on sys.USER$ to dbmonopr;
//授权同步账号查看数据库的系统表。sys.USER$是用户会话的默认服务。
grant select on sys.cdef$ to dbmonopr;
//授权同步账号查看数据库的系统表。
grant create session to dbmonopr;
//授权同步账号登录数据库。
grant select on sys.link$ to dbmonopr;
grant all on sys.user$ to dbmonopr;
grant all on sys.seq$ to dbmonopr;
grant all on sys.undo$ to dbmonopr;
grant connect to '同步账号'; //授权同步账号连接数据库。
grant select on nls_database_parameters to '同步账号'; //授权同步账号查询数据库的nls_database_parameters系统配置。
grant select on all_users to '同步账号'; //授权同步账号查询数据库中的所有用户。
grant select on all_objects to '同步账号'; //授权同步账号查询数据库中的所有对象。
grant select on DBA_MVIEWS to '同步账号'; //授权同步账号查看数据库的物化视图。
grant select on DBA_MVIEW_LOGS to '同步账号'; //授权同步账号查看数据库的物化视图日志。
grant select on DBA_CONSTRAINTS to '同步账号'; //授权同步账号查看数据库所有表的约束信息。
grant select on DBA_CONS_COLUMNS to '同步账号'; //授权同步账号查看数据库中所有表指定约束中所有列的相关信息。
grant select on all_tab_cols to '同步账号'; //授权同步账号查看数据库中表、视图和集群中列的相关信息。
grant select on sys.obj$ to '同步账号'; //授权同步账号查看数据库中的对象。sys.obj$表是Oracle字典表中的对象基础表，存放Oracle的所有对象。
grant select on SYS.COL$ to '同步账号'; //授权同步账号查看数据库表中列的定义信息。SYS.COL$用于保存表中列的定义信息。
grant select on sys.USER$ to '同步账号'; //授权同步账号查看数据库的系统表。sys.USER$是用户会话的默认服务。
grant select on sys.cdef$ to '同步账号'; //授权同步账号查看数据库的系统表。
grant select on sys.con$ to '同步账号'; //授权同步账号查看数据库的约束信息。sys.con$记录了Oracle的相关约束信息。
grant select on all_indexes to '同步账号'; //授权同步账号查看数据库的所有索引。
grant select on v_$database to '同步账号'; //授权同步账号查看数据库的v_$database视图。
grant select on V_$ARCHIVE_DEST to '同步账号'; //授权同步账号查看数据库的V_$ARCHIVE_DEST视图。
grant select on v_$log to '同步账号'; //授权同步账号查看数据库的v_$log视图。v_$log用于显示控制文件中的日志文件信息。
grant select on v_$logfile to '同步账号'; //授权同步账号查看数据库的v_$logfile视图。v_$logfile包含有关Redo日志文件的信息。
grant select on v_$archived_log to '同步账号'; //授权同步账号查看数据库的v$archived_log视图。v$archived_log包含有关归档日志的相关信息。
grant select on V_$LOGMNR_CONTENTS to '同步账号'; //授权同步账号查看数据库的V_$LOGMNR_CONTENTS视图。
grant select on DUAL to '同步账号'; //授权同步账号查看数据库的DUAL表。DUAL是用来构成select语法规则的虚拟表，Oracle的中DUAL中仅保留一条记录。
grant select on v_$parameter to '同步账号'; //授权同步账号查看数据库的v_$parameter视图。v$parameter是Oracle的动态字典表，保存了数据库参数的设置值。
grant select any transaction to '同步账号'; //授权同步账号查看数据库的任意事务。
grant execute on SYS.DBMS_LOGMNR to '同步账号'; //授权同步账号使用数据库的Logmnr工具。Logmnr工具可以帮助您分析事务，并找回丢失的数据。
grant alter session to '同步账号'; //授权同步账号修改数据库的连接。
grant select on dba_objects to '同步账号'; //授权同步账号查看数据库的所有对象。
grant select on v_$standby_log to '同步账号'; //授权同步账号查看数据库的v_$standby_log视图。v_$standby_log包含备用库的归档日志。
grant select on v_$ARCHIVE_GAP to '同步账号'; //授权同步账号查询缺失的归档日志。
#####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
###sample 3
- During the 19c upgrade for each job in DBMS_JOB a corresponding entry will be created with DBMS_SCHEDULER
Changes in 19c release
Support for DBMS_JOB
Oracle continues to support the DBMS_JOB package. However, you must grant the CREATE JOB privilege to the database schemas that submit DBMS_JOB jobs.
Oracle Scheduler replaces the DBMS_JOB package. Although DBMS_JOB is still supported for backward compatibility, Oracle strongly recommends that you switch from DBMS_JOB to Oracle Scheduler.
- During the 19c upgrade for each job in DBMS_JOB a corresponding entry will be created with DBMS_SCHEDULER
- The old DBMS_JOB interface still works. But using it will always create a corresponding entry in the scheduler
- Pre-upgrade check in preupgrade.jar checks for inconsistencies or any issues