物化视图测试手册-mysql教程-PHP中文网

物化视图测试手册

WBOY

发布： 2016-06-07 17:39:15

原创

838 人浏览过

《物化视图测试手册》场合：数据变化小，查询出数据还要2次利用，需要数据双向同步的场合视图：就是一条sql语句，每次查询时都要重新生成执行计划，重新执行，非

(

############################################################################################

select*fromALL_MVIEWS;

select*fromUSER_MVIEW_ANALYSIS;

insertintosino_person_addressvalues(seq_sino_person_address.nextval,123,to_date('2013-04-0812:12:12','yyyy-mm-ddhh24:mi:ss'),'110','test_report',111,'beijing

xicheng','100100','1',123,1,'1000',0);

insertintosino_person_addressvalues(seq_sino_person_address.nextval,123,to_date('2013-04-0912:12:12','yyyy-mm-ddhh24:mi:ss'),'120','test_report2',121,'beijing

xicheng','100200','2',123,1,'1002',2);

insertintosino_person_addressvalues(seq_sino_person_address.nextval,123,to_date('2013-04-1012:12:12','yyyy-mm-ddhh24:mi:ss'),'130','test_report3',131,'beijing

xicheng','100300','3',123,1,'1003',3);

commit

###################################################################################################

selectowner,table_name,tablespace_name,statusfromdba_tableswheretable_namein('SINO_LOAN_APPLY');

updateSINO_LOAN_APPLYsetsorgcode='1000'whereiid=858;

execdbms_mview.refresh('mv_sino_loan_compact','c');

execdbms_mview.refresh('mv_sino_loan_compact','f');

execdbms_mview.refresh('mv_sino_loan_apply','c');

execdbms_mview.refresh('mv_sino_loan_apply','f');

execdbms_mview.refresh('mv_sino_loan_spec_trade','c');

execdbms_mview.refresh('mv_sino_loan_spec_trade','f');

execdbms_mview.refresh('mv_sino_loan','c');

execdbms_mview.refresh('mv_sino_loan','f');

execdbms_mview.refresh('mv_sino_loan_guarantee','c');

execdbms_mview.refresh('mv_sino_loan_guarantee','f');

execdbms_mview.refresh('mv_sino_loan_investor','c');

execdbms_mview.refresh('mv_sino_loan_investor','f');

###############################################################################

execdbms_mview.refresh('mv_sino_person_employment','c');

execdbms_mview.refresh('mv_sino_person_employment','f');

execdbms_mview.refresh('mv_sino_person_address','c');

execdbms_mview.refresh('mv_sino_person_address','f');

execdbms_mview.refresh('mv_sino_person_certification','c');

execdbms_mview.refresh('mv_sino_person_certification','f');

execdbms_mview.refresh('mv_sino_person','c');

execdbms_mview.refresh('mv_sino_person','f');

3.ipbcstatenumber(1)

sino_person_certification

sino_person

sino_person_address

sino_person_employment

sino_person_address_his

sino_person_employment_his

sino_person_his

sino_loan

sino_loan_compact

sino_loan_spec_trade

sino_loan_guarantee

sino_loan_investor

sino_loan_apply

################################################################################

##################################################################################

dbms_output.put_line('JobNumberis'||to_char(job_num));

commit;

end;

dbms_output.put_line('JobNumberis'||to_char(job_num));

commit;

end;

Job作业唯一编号

Log_user提交作业的用户

What作业执行的存储过程

Last_date最后一次成功运行作业的日期

Last_sec最后一次成功运行作业的时间

Next_date下一次运行作业日期

Next_sec下一次运行作业时间

Failures执行失败次数，当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1，加到16之后Oracle就不在执行它了

Broken是否是异常作业，当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为notbroken，或强制调用dbms_job.run来重新执行它。Y标示作业中断，以后不会运行，N表示作业正常，可以运行

运行作业
begin
dbms_job.run(:job_num);job_num是存储job编号的变量
end;

查询作业状态

SQL>selectjob,log_user,what,last_date,last_sec,next_date,next_sec,failures,brokenfromuser_jobs;

JOBLOG_USERWHATLAST_DATELAST_SECNEXT_DATENEXT_SECFAILURESBROKEN

-------------------------------------------------------------------------------------------------------------

1SINOJFSpro_refresh_all_mviews;2013-4-26111:27:382013-4-27110:00:000N