Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係 Tweet

Previously on Mac De Oracle

前回は、Data PumpとMaterialized Viewという癖モノ二大巨頭を絡ませて見ました。

今回は、三つ巴？な感じでお送りしたいと思います。:)

---

以下は、Materialized ViewをリフレッシュするDBMS_JOBです。

今頃気づいたか！　という感じですが、dbms_job で作成されるJOBは、ALL/DBA/USER_OBJECTSには含まれないのです！！

orcl@USERS> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
        21 USERS                USERS                2017/04/09 19:18:34 2017/04/09 19:19:34 sysdate+1/1440                0 dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

orcl@USERS> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER                   MATERIALIZED VIEW
SYS_C0014637                   INDEX
MVIEW_MASTER                   TABLE
TOUSERM                        DATABASE LINK

ここで、注目！！
dbms_jobは、dba_objectsにはカウントされませんが、dbms_schedulerは、object_type=JOBとしてdba_objectsにカウントされるということ！！
以下にある、OBJECT_TYPE=JOBは、OBJECT_NAME=TESTという直前に作成されたdbms_schedulerのJOBだということ！！

ORCL@USERS> begin
  dbms_scheduler.create_job (
     job_name=>'test'
    ,job_type=>'PLSQL_BLOCK'
    ,job_action=>'BEGIN dbms_refresh.refresh(''USERS.MVIEW_MASTER''); END;'
    ,start_date=>systimestamp
    ,repeat_interval=>'FREQ=MONTHLY'
    ,end_date=>systimestamp + interval '1' year
    ,enabled=>true
    ,comments=>null
  );
end;
/

PL/SQL procedure successfully completed.

なんとまぁ、面倒くさい、DBMS_JOBのJOBも、DBMS_SCHEDULERのJOBも、同じ初期化パラメータ(job_queue_processes)を利用するのにオブジェクト扱いされたりされなかったり、面倒くさい癖モノです。注意しましょうね！！

Oracle® Databaseリファレンス　12c リリース1 (12.1) 1.126 JOB_QUEUE_PROCESSES
https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-B8B68D16-00A3-43DD-BE39-01F877880955.htm

ORCL@USERS> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER                   TABLE
SYS_C0014637                   INDEX
MVIEW_MASTER                   MATERIALIZED VIEW
TEST                           JOB
TOUSERM                        DATABASE LINK

という準備運動が終わったところで、本題の Data Pumpでのお話です。
癖モノData Pumpと癖モノ感たっぷりの２種類のJOB、２時間ドラマの複雑でドロドロした関係が予想される展開になってきましたw

orcl@USERS> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

       JOB LOG_USER             SCHEMA_USER          LAST_DATE           NEXT_DATE           INTERVAL               FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
        21 USERS                USERS                2017/04/09 19:18:34 2017/04/09 19:19:34 sysdate+1/1440                0 dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

orcl@USERS> select job_name,job_action,repeat_interval,start_date from user_scheduler_jobs;

JOB_NAME   JOB_ACTION                                                          REPEAT_INTERVAL      START_DATE
---------- ------------------------------------------------------------------- -------------------- ----------------------------------------
TEST       BEGIN dbms_refresh.refresh('USERS.MVIEW_MASTER'); END;              FREQ=MONTHLY         09-APR-17 10.36.05.814317 PM +09:00

まず、Data Pumpで扱える"OBJECT”が定義されているSCHEMA_EXPORT_OBJECTSをみると以下の"OBJECT”定義が見つかります。
JOBというオブジェクトのコメントを読んでも、２種類あるJOBのどちらを指しているのか、はたまた、いずれか一つなのかさっぱりわかりません。

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK           Private database links in the selected schemas  
SCHEMA_EXPORT/TABLE             Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB               Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP     Refresh groups in the selected schemas

これは試して、ガッテン！！　するしかありません！！！

schemaモードでエクスポートします。このとき、includeパラメータでJOBだけをエクスポートするよう指定します！！　

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orcl directory=homedir dumpfile=jobs.dmp logfile=jobsexp.log schemas=users include=job 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/JOB
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/jobs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 9 23:13:47 2017 elapsed 0 00:00:04

SQLFILEパラメータを指定して内容を確認してみます。（SQLFILEパラメータを指定したimpdpコマンドでは実際にインポートは行われません）

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/********@orcl directory=homedir dumpfile=jobs.dmp logfile=jobsexp.log schemas=users sqlfile=jobsexp_ddl.txt 
Processing object type SCHEMA_EXPORT/JOB
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sun Apr 9 23:20:44 2017 elapsed 0 00:00:03

おおおおお、これは！！！　

なんということでしょう!
Data Pumpでは、JOBオブジェクトは、DBMS_JOBのJOBオブジェクト（dba_objectsではオブジェクト扱いされていないのに！！）

だとすると、dba_objectsでは”OBJECT"として扱われている、DBMS_SCHEDULERのJOBはの扱いはいかに。。。

以下のとおり、DBMS_IJOBと内部的プロリージャに置き換わっていますが、パラメータを見れば一目瞭然、DBMS_JOBの定義しかありません。
つまり、Data PumpのJOBオブジェクトは、DBMS_JOBのJOBであることがわかりました！！

[oracle@catfish ˜]$ cat jobsexp_ddl.txt 

・・・中略・・・

-- new object type path: SCHEMA_EXPORT/JOB
 BEGIN SYS.DBMS_IJOB.SUBMIT(
      JOB=> 21,
      LUSER=> 'USERS',
      PUSER=> 'USERS',
      CUSER=> 'USERS',
      NEXT_DATE=> TO_DATE('2017-04-09 23:13:51', 'YYYY-MM-DD:HH24:MI:SS'),
      INTERVAL=> 'sysdate+1/1440',
      BROKEN=>  FALSE,
      WHAT=> 'dbms_refresh.refresh(''"USERS"."MVIEW_MASTER"'');',

・・・中略・・・

では、DBMS_SCHEDULERのJOBは。。。それは。。。PROCOBJというOBJECT_PATHに含まれているようで（ほぼ誰にも読み取れないw）。。。

MOSにもありそうだけど、この辺りをまとめてて疲れて、めんどくさい病の発作がw　
もっと深掘りする気力があったら探すかもw
Export/Import Scheduler Jobs

手取り早く、試して、ガッテン！ includeパラメータで”PROCOBJ”を指定します。

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orcl directory=homedir dumpfile=scheduler_jobs.dmp logfile=scheduler_jobsexp.log schemas=users include=procobj 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scheduler_jobs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 9 23:49:20 2017 elapsed 0 00:00:04

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/********@orcl directory=homedir dumpfile=scheduler_jobs.dmp logfile=jobsddldump.log schemas=users sqlfile=jobsexp2_ddl.txt 
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sun Apr 9 23:50:05 2017 elapsed 0 00:00:02

確かに、PROCOBJにDBMS_SCHEDULERのJOBが含まれている！！

[oracle@catfish ˜]$ cat jobsexp2_ddl.txt

・・・中略・・・

BEGIN 
dbms_scheduler.create_job('"TEST"',
job_type=>'PLSQL_BLOCK'
, job_action=>'BEGIN dbms_refresh.refresh(''USERS.MVIEW_MASTER''); END;'
, number_of_arguments=>0
, start_date=>TO_TIMESTAMP_TZ('09-APR-2017 10.36.05.814317000 PM +09:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english')
, repeat_interval=> 'FREQ=MONTHLY'
, end_date=>TO_TIMESTAMP_TZ('09-APR-2018 10.36.05.814344000 PM +09:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english')
, job_class=>'"DEFAULT_JOB_CLASS"'
, enabled=>FALSE
, auto_drop=>TRUE
,comments=>NULL
);
dbms_scheduler.enable('"TEST"');
COMMIT; 
END; 
/ 

・・・中略・・・

登場人物の関係が複雑すぎてよくわからなくなってきたので、まとめ。

DBMS_JOBのJOBは、DBA_OBJECTS上、オブジェクトとは扱われていない。
DBMS_SCHEDULEのJOBは、DBA_OBJECTS上、オブジェクトとして扱われている。
どちらのJOBも、初期化パラメータ、job_queue_processesにより制御されている。
Data PumpのSCHEMA_EXPORT_OBJECTSにて定義されているJOBオブジェクトは、DBMS_JOBのJOBのことである。
Data PumpのSCHEMA_EXPORT_OBJECTSにて定義されているPROCOBJオブジェクトが、DBMS_SCHEDULERのJOBのこと！？であるようだ。
（オブジェクト名からは想像できない可読性の悪さはなんとかしてくれ！）

DBMS_SCHEDULERへの移行が推奨されながらDBMS_JOBが未だに存在していることの弊害のようにも思えてきた。
このあたり、理解しやすいように改善してもらいたい癖モノの一つとしてリストに加えておこう。

---

満開の桜なのに残念な天気の日曜日終わってしまった！！
ということで今日はここまで。

---

Data Pumpも癖モノだよね〜w　その１ - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w　その２ - Materialized ViewをTableとして移行する