[20211206]修改job屬性問題.txt

[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修改失效。