背景:工作中需要使用新的scheme去上传、更新另一个scheme下的对象,话说这么做本身就不太合适但是出于安全等各种原因吧事实就是这样了,DML
DDL都可以通过赋权限做到,遇到的一个难题是:如果管理另一个scheme下的job?据我之前的了解,oracle的job只是当前scheme可以管理,sys都不能管理另外一个scheme下的job;现在发现,这个认识是需要更正的,正确的说法是,通过dbms_job包不能管理其他scheme下的job;解决的方法就是使用oracle文档未支持的dbma_ijob包,该包需要通过sys用户给使用者赋权限;
desc sys.dbms_ijob;
FUNCTION BIS RETURNS NUMBER
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
FLAG
NUMBER
IN
VAL
NUMBER
IN
VAL2
BOOLEAN
IN
FUNCTION BIT RETURNS BOOLEAN
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
FLAG
NUMBER
IN
VAL
NUMBER
IN
PROCEDURE BROKEN
参数名称
类型输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
BROKEN
BOOLEAN
IN
NEXT_DATE
DATE
IN
DEFAULT
PROCEDURE CHANGE_ENV
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
LUSER
VARCHAR2
IN
PUSER
VARCHAR2
IN
CUSER
VARCHAR2
IN
NLSENV
VARCHAR2
IN
PROCEDURE CHECK_PRIVS
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
FUNCTION DB_COMPATIBILITY RETURNS NUMBER
PROCEDURE DROP_USER_JOBS
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
MYUSER
VARCHAR2
IN
FUNCTION ENABLED RETURNS BOOLEAN
PROCEDURE EXPORT_MYINST
参数名称类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
MYINST
VARCHAR2
IN/OUT
PROCEDURE FULL_EXPORT
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
MYCALL
VARCHAR2
IN/OUT
PROCEDURE FULL_EXPORT
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
MYCALL
VARCHAR2
IN/OUT
MYINST
VARCHAR2
IN/OUT
PROCEDURE INSTANCE
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
INSTANCE
BINARY_INTEGER
IN
FORCE
BOOLEAN
IN
PROCEDURE INST_CHECK
参数名称
类型输入/输出默认值?
------------------------------ -----------------------
------ --------
INSTANCE
NUMBER
IN
FORCE
BOOLEAN
IN
PROCEDURE INTERVAL
参数名称类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
INTERVAL
VARCHAR2
IN
PROCEDURE NEXT_DATE
参数名称类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
NEXT_DATE
DATE
IN
FUNCTION PUID RETURNS NUMBER
FUNCTION PUSER RETURNS VARCHAR2
PROCEDURE REMOVE
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
PROCEDURE RUN
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
DEFAULT
FORCE
BOOLEAN
IN
DEFAULT
PROCEDURE SET_ENABLED
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
ENABLED
BOOLEAN
IN
PROCEDURE SUBMIT
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
LUSER
VARCHAR2
IN
PUSER
VARCHAR2
IN
CUSER
VARCHAR2
IN
NEXT_DATE
DATE
IN
INTERVAL
VARCHAR2
IN
BROKEN
BOOLEAN
IN
WHAT
VARCHAR2
IN
NLSENV
VARCHAR2
IN
ENV
RAW
IN
FUNCTION TOGGLE RETURNS NUMBER
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
FLAG
NUMBER
IN
VAL
NUMBER
IN
PROCEDURE WHAT
参数名称
类型
输入/输出默认值?
------------------------------ -----------------------
------ --------
JOB
BINARY_INTEGER
IN
WHAT
VARCHAR2
IN
常用的broken run next_date what
interval跟dbms_job类似,但是dbms_ijob.submit的最后两个参数没弄明白是做什么的,经过查找资料找到了一个替代的办法,即使用dbms_ijob.full_export导出创建job的脚本再执行submit;具体步骤如下:
创建dbms_ijob.full_export.sql,代码如下:
variable job number
variable submit_call varchar2(4000)
variable instance_call varchar2(4000)
exec :job:=&job_number;
begin
sys.dbms_ijob.full_export(:job,:submit_call,:instance_call);
end;
/
SQL> @dbms_ijob.full_export.sql;
输入 job_number 的值: 7673
PL/SQL 过程已成功完成。
SQL> print submit_call
SUBMIT_CALL
--------------------------------------------------------------------------------
sys.dbms_ijob.submit(job=>7673,luser=>'EDU',puser=>'EDU',cuser=>'EDU',next_date=
>to_date('2014-07-13:01:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'TRUNC(SYSDATE
+1)+1/24',broken=>FALSE,what=>'PROC_GRADUATE_UNIT_ATTIME;
',nlsenv=>'NLS_LANGUAGE=''SIMPLIFIED CHINESE''
NLS_TERRITORY=''CHINA'' NLS_CURRE
NCY=''¥'' NLS_ISO_CURRENCY=''CHINA'' NLS_NUMERIC_CHARACTERS=''.,''
NLS_DATE_FOR
MAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''
NLS_SORT=''BINARY''',
env=>'0102000200000000');
脚本:
begin
sys.dbms_ijob.submit
(1,
'EDU',
'EDU',
'EDU',
to_date('2014-07-13:01:00:00','YYYY-MM-DD:HH24:MI:SS'),
'TRUNC(SYSDATE+1)+1/24',
FALSE,
'a_test1;',
'NLS_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_TERRITORY=''CHINA''
NLS_CURRE
NCY=''¥'' NLS_ISO_CURRENCY=''CHINA'' NLS_NUMERIC_CHARACTERS=''.,''
NLS_DATE_FOR
MAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''
NLS_SORT=''BINARY''',
'0102000200000000');
end;
begin
sys.dbms_ijob.next_date
(1,trunc(sysdate+1)+2/24);
end;
begin
sys.dbms_ijob.interval
(1,'trunc(sysdate+1)+2/24');
end;
begin
sys.dbms_ijob.what
(1,'a_test2');
end;
begin
sys.dbms_ijob.broken
(1,true);
end;
begin
sys.dbms_ijob.broken
(1,false,trunc(sysdate+1));
end;
begin
sys.dbms_ijob.remove
(1);
end;
参考资料:未公开的Oracle数据库秘密