[20211206]修改job属性问题.txt

来源：这里教程网时间：2026-03-03 17:16:42 作者：

[20211206]修改job属性问题.txt --//修改job属性遇到的问题，做一个记录。 --//自己很少涉及这方面工作。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production DECLARE X NUMBER; user_name varchar2(30); BEGIN select user into user_name from dual; execute immediate 'alter session set current_schema = SCOTT'; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'SCOTT.TEST_JOB_DEPTX;' ,next_date => to_date('05/12/2021 09:35:55','dd/mm/yyyy hh24:mi:ss') ,interval => 'SYSDATE+5/1440 ' ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); execute immediate 'alter session set current_schema = ' || user_name ; EXCEPTION WHEN OTHERS THEN execute immediate 'alter session set current_schema = ' || user_name ; RAISE; END; COMMIT; END; / 2.如果我设置offline属性，跟踪执行如下： BEGIN SYS.DBMS_JOB.BROKEN('415',TRUE); END; --//我发现退出后，设置失效，原因在于由于工作的特殊性，我使用toad缺省设置都是 view->toad options -> Oracle -> Transactions 选择 Rollback (when close connections). --//也就是要生效要执行一个提交操作。 2.我使用10046跟踪看看。 SCOTT@book> @46on 12 Session altered. SCOTT@book> BEGIN SYS.DBMS_JOB.BROKEN('415',TRUE); END; 2 / PL/SQL procedure successfully completed. SCOTT@book> @46off Session altered. SCOTT@book> @ ttt tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39343.trc $ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39343.trc BEGIN SYS.DBMS_JOB.BROKEN('415',TRUE); END; SELECT NAME FROM SYS.USER$ WHERE USER# = :B1 SELECT 1 FROM SYS.JOB$ WHERE JOB = :B3 AND POWNER = :B2 AND (SCHEDULER_FLAGS IS NULL OR BITAND(SCHEDULER_FLAGS, :B1 ) != :B1 ) FOR UPDATE SELECT POWNER FROM JOB$ WHERE JOB = :B1 SELECT FLAG FROM SYS.JOB$ WHERE JOB = :B1 UPDATE SYS.JOB$ J SET FLAG = :B4 , NEXT_DATE = NVL(:B3 , J.NEXT_DATE) WHERE JOB = :B2 AND (:B1 = 'SYS' OR J.POWNER != 'SYS') alter session set events '10046 trace name context off' ===================== PARSING IN CURSOR #139714858814424 len=124 dep=1 uid=0 oct=6 lid=0 tim=1638760332370498 hv=1374314909 ad='7bfb7658' sqlid='7vbdnn98ynscx' UPDATE SYS.JOB$ J SET FLAG = :B4 , NEXT_DATE = NVL(:B3 , J.NEXT_DATE) WHERE JOB = :B2 AND (:B1 = 'SYS' OR J.POWNER != 'SYS') END OF STMT PARSE #139714858814424:c=0,e=271,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1638760332370496 BINDS #139714858814424: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=206001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f11e6878790 bln=22 avl=02 flg=05 value=1 --//设置flag=1. Bind#1 oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0 kxsbbbfp=7f11e68b24e0 bln=07 avl=07 flg=09 value="1/1/4000 0:0:0" --//设置next_date = 4000/1/1. Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=206001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f11e6878760 bln=22 avl=03 flg=05 value=415 Bind#3 oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0 kxsbbbfp=7f11e68b2370 bln=32 avl=05 flg=09 value="SCOTT" EXEC #139714858814424:c=2000,e=1752,p=0,cr=2,cu=5,mis=1,r=1,dep=1,og=1,plh=3532225794,tim=1638760332372347 STAT #139714858814424 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE JOB$ (cr=2 pr=0 pw=0 time=246 us)' STAT #139714858814424 id=2 cnt=1 pid=1 pos=1 obj=288 op='TABLE ACCESS BY INDEX ROWID JOB$ (cr=2 pr=0 pw=0 time=16 us cost=1 size=26 card=1)' STAT #139714858814424 id=3 cnt=1 pid=2 pos=1 obj=289 op='INDEX UNIQUE SCAN I_JOB_JOB (cr=1 pr=0 pw=0 time=9 us cost=0 size=0 card=1)' CLOSE #139714858814424:c=0,e=2,dep=1,type=3,tim=1638760332372505 EXEC #139714858585144:c=10999,e=11114,p=0,cr=14,cu=8,mis=0,r=1,dep=0,og=1,plh=0,tim=1638760332372545 WAIT #139714858585144: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1638760332372630 *** 2021-12-06 11:12:18.326 WAIT #139714858585144: nam='SQL*Net message from client' ela= 5953372 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1638760338326034 CLOSE #139714858585144:c=0,e=28,dep=0,type=0,tim=1638760338326165 ===================== SCOTT@book> @ desc sys.job$ Name Null? Type ------------------------------- -------- ---------------------------- 1 JOB NOT NULL NUMBER 2 LOWNER NOT NULL VARCHAR2(30) 3 POWNER NOT NULL VARCHAR2(30) 4 COWNER NOT NULL VARCHAR2(30) 5 LAST_DATE DATE 6 THIS_DATE DATE 7 NEXT_DATE NOT NULL DATE 8 TOTAL NOT NULL NUMBER 9 INTERVAL# NOT NULL VARCHAR2(200) 10 FAILURES NUMBER 11 FLAG NOT NULL NUMBER 12 WHAT VARCHAR2(4000) 13 NLSENV VARCHAR2(4000) 14 ENV RAW(32) 15 CUR_SES_LABEL MLSLABEL 16 CLEARANCE_HI MLSLABEL 17 CLEARANCE_LO MLSLABEL 18 CHARENV VARCHAR2(4000) 19 FIELD1 NUMBER 20 SCHEDULER_FLAGS NUMBER 21 XID VARCHAR2(40) --//job$ 有几个数据类型MLSLABEL。 SCOTT@book> select * from sys.job$ 2 @ pr ORA-06512: at "SYS.DBMS_SQL", line 1825 ORA-06512: at line 17 declare * ERROR at line 1: ORA-00932: inconsistent datatypes: expected CHAR got LABEL ORA-06512: at line 31 --//报错。 SCOTT@book> select job,flag,NEXT_DATE from sys.job$ ; JOB FLAG NEXT_DATE ---------- ---------- ------------------- 4001 0 2021-12-06 16:50:57 4002 0 2021-12-06 11:21:06 415 1 4000-01-01 00:00:00 SCOTT@book> rollback ; Rollback complete. SCOTT@book> select job,flag,NEXT_DATE from sys.job$ ; JOB FLAG NEXT_DATE ---------- ---------- ------------------- 4001 0 2021-12-06 16:50:57 4002 0 2021-12-06 11:21:06 415 0 2021-12-06 11:25:17 --//可以发现如果关闭时选择rollback，job修改失效。