add 10046 into procedure

admin

1. CREATE OR REPLACE PROCEDURE ZHYU.REFRESH_MV as
   
2. BEGIN
   
3. EXECUTE IMMEDIATE 'alter session set timed_statistics = true';
   
4. EXECUTE IMMEDIATE 'alter session set statistics_level=all';
   
5. EXECUTE IMMEDIATE 'alter session set MAX_DUMP_FILE_SIZE=UNLIMITED';
   
6. EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
   
7. EXECUTE IMMEDIATE 'alter session set tracefile_identifier="10046"';
   
8. dbms_mview.refresh('MV_T');
   
9. EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context off''';
   
10. END;
    
11. /
    

复制代码

///在pdb1创建zhyu用户，并赋权

1. sqlplus sys/oracle@pdb1 as sysdba;
   
2. create user zhyu identified by zhyu;
   
3. grant connect,resource to zhyu;
   
4. grant scheduler_admin to zhyu;
   
5. grant unlimited tablespace to zhyu;
   
6. grant create materialized view to zhyu;
   
7. grant alert session to zhyu;

复制代码

///创建测试表

1. sqlplus zhyu/zhyu@pdb1;
   
2. create table t(time date);

复制代码

///创建2个调度，一个间隔1分钟，一个间隔5分钟

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
   
4. start_date => to_date('13-03-2023 15:00:00', 'dd-mm-yyyy hh24:mi:ss'),
   
5. comments => 'scheduler by minute for zhyu',
   
6. schedule_name => '"zhyu_min"');
   
7. END;
   
8. /

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
   
4. start_date => to_date('13-03-2023 15:25:00', 'dd-mm-yyyy hh24:mi:ss'),
   
5. comments => 'scheduler by minute for zhyu',
   
6. schedule_name => '"zhyu_5min"');
   
7. END;
   
8. /
   

复制代码

///创建2个物化视图，创建一个刷物化的过程

1. create materialized view mv_1_t
   
2. refresh force on demand as
   
3. select * from t;
   
4. exec dbms_mview.refresh('MV_1_T');

复制代码

1. create materialized view mv_2_t
   
2. refresh force on demand as
   
3. select * from t;
   
4. exec dbms_mview.refresh('MV_2_T');

复制代码

1. create or replace procedure refresh_mv as begin
   
2. dbms_mview.refresh('MV_1_T');
   
3. dbms_mview.refresh('MV_2_T');
   
4. end;
   
5. /

复制代码

///创建2个作业，每1分钟插入t表时间戳，每5分钟执行刷物化的过程

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"ZHYU_JOB1"',
   
4. job_type => 'PLSQL_BLOCK',
   
5. job_action => 'begin
   
6.    insert into t select sysdate from dual;
   
7. end;',
   
8. schedule_name => '"zhyu_min"',
   
9. job_class => '"DEFAULT_JOB_CLASS"',
   
10. comments => 'insert into t values to t',
    
11. auto_drop => FALSE,
    
12. enabled => FALSE);
    
13. dbms_scheduler.set_attribute( name => '"ZHYU_JOB1"', attribute => 'job_priority', value => 4);
    
14. dbms_scheduler.enable( '"ZHYU_JOB1"' );
    
15. END;
    
16. /

复制代码

1. BEGIN
   
2. dbms_scheduler.create_job(
   
3. job_name => '"ZHYU_JOB2"',
   
4. job_type => 'PLSQL_BLOCK',
   
5. job_action => 'begin
   
6. refresh_mv;
   
7. end;',
   
8. schedule_name => '"zhyu_5min"',
   
9. job_class => '"DEFAULT_JOB_CLASS"',
   
10. comments => 'insert into t values to t',
    
11. auto_drop => FALSE,
    
12. enabled => FALSE);
    
13. dbms_scheduler.set_attribute( name => '"ZHYU_JOB2"', attribute => 'job_priority', value => 4);
    
14. dbms_scheduler.enable( '"ZHYU_JOB2"' );
    
15. END;
    
16. /

复制代码

///修改过程加入10046

1. create or replace procedure refresh_mv as begin
   
2. EXECUTE IMMEDIATE 'alter session set timed_statistics = true';
   
3. EXECUTE IMMEDIATE 'alter session set statistics_level=all';
   
4. EXECUTE IMMEDIATE 'alter session set MAX_DUMP_FILE_SIZE=UNLIMITED';
   
5. EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
   
6. EXECUTE IMMEDIATE 'alter session set tracefile_identifier="10046"';
   
7. dbms_mview.refresh('MV_1_T');
   
8. dbms_mview.refresh('MV_2_T');
   
9. EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context off''';
   
10. END;
    
11. /

复制代码

///查看作业执行情况

1. ZHYU@pdb1> select JOB_NAME,START_DATE,ENABLED,STATE,LAST_START_DATE,NEXT_RUN_DATE from user_scheduler_jobs;
   
2. 
   
3. JOB_NAME               START_DATE        ENABL STATE            LAST_START_DATE                                   NEXT_RUN_DATE
   
4. ------------------------------ -------------------- ----- -------------------- ---------------------------------------------------------------------------
   
5. ZHYU_JOB1               13-MAR-23 03.02.00.9 TRUE  SCHEDULED           13-MAR-23 04.39.01.033777 PM +08:00                       13-MAR-23 04.40.00.0
   
6.                    58782 PM +08:00                                                           40010 PM +08:00
   
7. 
   
8. ZHYU_JOB2               13-MAR-23 04.00.00.0 TRUE  SCHEDULED           13-MAR-23 04.35.00.022577 PM +08:00                       13-MAR-23 04.40.00.0
   
9.                    00000 PM +08:00  
   

复制代码

///查看trace目录的10046.trc

1. [oracle@vm1 trace]$ ll -lrt|grep 10046
   
2. -rw-rw---- 1 oracle oracle  434356 Mar 13 16:25 ora19_j000_22304_10046.trm
   
3. -rw-rw---- 1 oracle oracle 2202362 Mar 13 16:25 ora19_j000_22304_10046.trc
   
4. -rw-rw---- 1 oracle oracle   98243 Mar 13 16:30 ora19_j000_24514_10046.trm
   
5. -rw-rw---- 1 oracle oracle  428097 Mar 13 16:30 ora19_j000_24514_10046.trc
   
6. -rw-rw---- 1 oracle oracle   78026 Mar 13 16:35 ora19_j000_26724_10046.trm
   
7. -rw-rw---- 1 oracle oracle  337752 Mar 13 16:35 ora19_j000_26724_10046.trc
   
8. -rw-rw---- 1 oracle oracle  302941 Mar 13 16:40 ora19_j000_28935_10046.trm
   
9. -rw-rw---- 1 oracle oracle 1512982 Mar 13 16:40 ora19_j000_28935_10046.trc

复制代码