《物化视图测试手册》
场合：数据变化小，查询出数据还要2次利用，需要数据双向同步的场合
视图：就是一条sql语句，每次查询时都要重新生成执行计划，重新执行，非常消耗时间，放在内存中一次性的
物化视图：执行sql并保留结果，直接放在数据文件中，不放在内存中方便重用【空间换时间】，不受开关机的影响
【问】创建具有快速刷新功能的物化视图，要求物化视图能消除表中行的重复值
【参】Books->DataWarehousingGuide->8BasicMaterializedViews和9AdvancedMaterializedViews
【参】Books->SQLReference->检索关键字“CREATELIBRARY”和“CREATEMATERIALIZEDVIEW”
【参】Books->PL/SQLPackagesandTypesReference->61DBMS_MVIEW
【答】
selectdf.tablespace_name"表空间名",totalspace"总空间M",freespace"剩余空间M",round((1-freespace/totalspace)*100,2)"使用率%"
from
(selecttablespace_name,round(sum(bytes)/1024/1024)totalspacefromdba_data_filesgroupbytablespace_name)df,
(selecttablespace_name,round(sum(bytes)/1024/1024)freespacefromdba_free_spacegroupbytablespace_name)fs
wheredf.tablespace_name=fs.tablespace_nameorderbydf.tablespace_name;--表空间使用率
表空间名总空间M剩余空间M使用率%
------------------------------------------------------------
DATA_SPACE335017394.84
EC_DATA1000099840.16
SINOJFS200019940.3
SINOJFS210240102390.01
SYSAUX60029550.83
SYSTEM70034950.14
UNDOTBS1208520720.62
USERS5420
数据文件路径
/opt/oracle/oradata/base/sinojfs01.dbf
droptablespacesinojfs2includingcontentsanddatafiles；删除表空间(包括内容和数据文件一次性删除)
创建表空间
createtablespacesinojfs2datafile'/opt/oracle/oradata/base/sinojfs02.dbf'size10Gautoextendoff;非自动扩展
Tablespacecreated.
SQL>selectsegment_name,tablespace_namefromdba_segmentswheresegment_name='MV_SINO_PERSON_ADDRESS';
SEGMENT_NAMETABLESPACE_NAME
-------------------------------------------------------------------------------------------
MV_SINO_PERSON_ADDRESSSINOJFS2
SQL>selectindex_name,index_type,table_name,tablespace_namefromdba_indexeswheretable_name='MV_SINO_PERSON_ADDRESS';物化视图创建后自带主键，并且物化视图和主键都在一个表空间
INDEX_NAMEINDEX_TYPETABLE_NAMETABLESPACE_NAME
-----------------------------------------------------------------------------------------------------
PK_SINO_PERSON_ADDRESS1NORMALMV_SINO_PERSON_ADDRESSSINOJFS2
###########################################################################################################
SQL>selectindex_name,index_type,table_name,tablespace_namefromdba_indexeswheretable_name='SINO_PERSON_ADDRESS';
基表和主键在另一个表空间
INDEX_NAMEINDEX_TYPETABLE_NAMETABLESPACE_NAME
---------------------------------------------------------------------------------------
PK_SINO_PERSON_ADDRESSNORMALSINO_PERSON_ADDRESSSINOJFS
############################################################################################
select*fromALL_MVIEWS;
select*fromUSER_MVIEW_ANALYSIS;
1.创建基表并插入数据
createtablesino_person_address
(
iidNUMBER(16)notnull,
ipersonidNUMBER(16),
spinNUMBER(16),
dgettimeDATE,
sorgcodeVARCHAR2(20),
smsgfilenameVARCHAR2(20),
ilinenoNUMBER(8),
saddressVARCHAR2(60),
szipCHAR(6),
sconditionCHAR(1),
itrustNUMBER(1),
stoporgcodeVARCHAR2(14),
istateNUMBER(1),
constraintPK_SINO_PERSON_ADDRESSprimarykey(iid)
);
插入数据(插入自动增长序列号字段的方法)
INSERT语句插入这个字段值为:序列号的名称.NEXTVAL,seq_sino_person_address.nextval
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
###################################################################################################
2.创建物化视图日志
意义：记录基表DML操作的变化，实时刷新物化视图
注：包含所有字段
删除物化视图日志
dropmaterializedviewlogont
创建物化视图日志
creatematerializedviewlogontwithsequence,rowid(x,y,z)includingnewvalues;
参数说明：
withsequence：以序号增1的方式进行变化记录
rowid(x,y,z)：定位哪些数据发生了变化，日志记录rowid指向的数据块的位置和变化
删除物化视图日志
dropmaterializedviewlogonsino_person_address;
基于主键方式的刷新，创建物化视图日志
CREATEMATERIALIZEDVIEWLOGONsino_person_address
WITHPRIMARYKEY
INCLUDINGNEWVALUES
【TABLESPACEsinojfs2】;可选项
3.创建物化视图
创建物化视图
creatematerializedviewmv_tbuildimmediaterefreshfastoncommitenablequeryrewriteasselectx,y,z,count(*)fromtgroupbyx,y,z;
删除物化视图
dropmaterializedviewmv_sino_person_address;
creatematerializedviewmv_sino_person_address
tablespaceSINOJFS2
buildimmediate创建物化视图时，立即刷新基表
refreshfastwithprimarykey支持基于主键的快速刷新（增量刷新），基表必须有主键
oncommit支持commit动作自动刷新
enablequeryrewrite
asselect*fromsino_person_address;
creatematerializedviewmv_sino_person_address
tablespaceSINOJFS2
buildimmediate
refreshfastwithprimarykeyrefreshcomplete全部刷新【全表刷新】可选项
ondemand支持需求时手工刷新
enablequeryrewrite
asselect*fromsino_person_address;
########################################################################################
参数说明：
buildimmediate：创建物化视图时，立即刷新基表
refreshfastwithprimarykey：支持基于主键的快速刷新（增量刷新），基表必须有主键
oncommit：基于commit动作的自动刷新ondemand：基于需求时的手工刷新
enablequeryrewrite：支持查询重新（使用物化视图代替基表，查询必须重写，查询重写是透明的并且不需要对物化视图有任何权限，物化视图可以启用和禁用查询重写）
查询重写：select*fromt基表，执行计划走的是mv_t物化视图，禁用后，执行计划走的就是t基表了
tablespaceSINOJFS2创建于SINOJFS2表空间
(1)创建方式：BUILDIMMEDIATE（立即生成数据），BUILDDEFERRED（下一次刷新时生新数据），ONPREBUILDTABLE（不创建新的数据段，用已存在的含有当前物化视图数据的表来代替）；
(2)ENABLE|DISABLEQUERYREWRITE指定是否启用当前物化视图用于查询重写，启用该选项时，系统会检查以保证查询的可确定性（不允许有如序列数，USER，DATE等不确定的返回值），DISABLE时物化视图照样可以被刷新；
与物化视图生效相关的设置
(1)初始化参数JOB_QUEUE_PROCESSES设置大于零，物化的自动刷新操作需要JOBQUEUE进程来执行；
(2)初始化参数OPTIMIZER_MODE要设成某种CBO优化模式；
(3)用户会话有QUERY_REWRITE（优化器能将查询重写到本方案物化视图）或GLOBAL_QUERY_REWRITE（优化器能将查询重写到其它方案的物化视图）系统权限；
(4)初始化参数QUERY_REWRITE_ENABLED指示优化器是否动态重写查询来使用物化视图，这个参数可以在四个级别上进行设置（参数文件，ALTERSYSTEM，ALTERSESSION，HINTS）；
(5)初始化参数QUERY_REWRITE_INTEGRITY指示优化器在不同的数据一致性情况下决定是否使用物化视图来重写查询，ENFORCED（只有在能确保数据一致的前提下才使用物化视图），TRUSTED（数据不一定一致，只要有用维度对象定义的关系存在，就可使用物化视图），STALE_TOLERATED（数据不一致，也没有相关的维度定义时仍可使用物化视图），这个参数可以在三个级别上进行设置（参数文件，ALTERSYSTEM，ALTERSESSION）；
4.物化视图DML操作测试
（1）验证物化视图是否随记录增加而增加
insertintosino_person_addressvalues(seq_sino_person_address.nextval,123,to_date('2013-04-1113:13:13','yyyy-mm-ddhh24:mi:ss'),'140','test_report4',141,'beijing
xicheng','100400','4',123,1,'1004',4);
select*fromsino_person_addressorderbydgetdate;
select*frommv_sino_person_addressorderbydgetdate;随记录增加而木有刷新，必须commit之后才触发物化视图刷新，没有问题
execdbms_mview.refresh('mv_sino_person_address','c');还可以手动全部刷新【全表刷新】（先清除，再重装数据）
execdbms_mview.refresh('mv_sino_person_address','f');也可以快速刷新【增量刷新】借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（2）验证物化视图是否随记录删除而减少
deletefromsino_person_addresswhereiid=21;
select*fromsino_person_addressorderbydgetdate;
select*frommv_sino_person_addressorderbydgetdate;随记录删除而木有刷新，必须commit之后才触发物化视图刷新，没有问题
execdbms_mview.refresh('mv_sino_person_address','c');还可以手动全部刷新【全表刷新】（先清除，再重装数据）
execdbms_mview.refresh('mv_sino_person_address','f');也可以快速刷新【增量刷新】借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（3）验证物化视图是否随记录修改而更新
updatesino_person_addresssetsorgcode='200'wheresorgcode='120';
select*fromsino_person_addressorderbydgetdate;
select*frommv_sino_person_addressorderbydgetdate;随记录修改而木有刷新，必须commit之后才触发物化视图刷新，没有问题
execdbms_mview.refresh('mv_sino_person_address','c');还可以手动全部刷新【全表刷新】（先清除，再重装数据）
execdbms_mview.refresh('mv_sino_person_address','f');也可以快速刷新【增量刷新】（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
（4）验证物化视图是否随truncate而清空
truncatetablesino_person_address;
select*fromsino_person_addressorderbydgetdate;
select*frommv_sino_person_addressorderbydgetdate;随记录truncate而木有清空，必须手动truncatetablemv_sino_person_address;才能清空（两者是没有关联的），没有问题
5.物化视图刷新
根据业务需求，每月定时刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。我们可以写存储过程，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。
定义存储过程
createorreplaceprocedurepro_mview_refresh
as
begin
dbms_mview.refresh('mv_sino_person_address','f');
end;
/
执行存储过程
executepro_mview_refresh;
还可以刷新所有物化视图dbms_mview.refresh_all_mviews;
创建存储过程
dropprocedurepro_refresh_all_mviews;
createorreplaceprocedurepro_refresh_all_mviews
as
inumber;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
执行
executepro_refresh_all_mviews;
setserveroutputon;不可放在存储过程中，因为这是sqlplus命令，如果你怕忘记或者嫌麻烦可以把setserveroutputon;
写入/opt/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql中，每次使用sqlplus时自动加载这个文件
如果想用PL/SQLDeveloper工具访问数据库，请在C:\ProgramFiles\PLSQLDeveloper\Login.sql文件里添加
--AutostartCommandWindowscript
setserveroutputon;
这样以后再使用PL/SQLDeveloper工具访问数据库就可以自动加载这条命令了
###############################################################################################
研发人员专用，手动刷新，想刷就刷
setserveroutputon;打开屏幕显示功能，就可以看到number_of_failures=>0结果
PL/SQL匿名块
declare
inumber;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
number_of_failures=>0
Number_of_failures表示刷新物化视图失败个数
采用默认refreshforce刷新方式：先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式
注意:
1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新，从不刷新）。
NEVERREFRESH（不刷新）
REFRESTFAST（借助物化视图日志，只检查自上次刷新后改变了的数据来进行刷新）
REFRESHCOMPLETE（先清除，再重装数据）
REFRESHFORCE（先试图用FAST方式刷新，如果失败再用COMPLETE方式刷新，这是默认的刷新方式）
确定刷新时机：
ONCOMMIT（事务提交时刷新），
ONDEMAND（用DBMS_MVIEW.REFRESH，DBMS_MVIEW.REFRESH_DEPENDENT，DBMS_MVIEW.REFRESH_ALL_MVIEWS来手工刷新），
ByTime（用STARTWITH和NEXT子句创建的job来定时自动刷新）；
[dbms_mview.refresh('mv_sino_person_address,mv_person_address_his','ff');]
2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量刷新。
dropmaterializedviewlogonsino_person_address;删除日志
SQL>execdbms_mview.refresh('mv_sino_person_address','c');删除物化视图日志，只可以支持物化视图全部刷新
PL/SQLproceduresuccessfullycompleted
#################################################################################
SQL>execdbms_mview.refresh('mv_sino_person_address','f');无法增量刷新
begindbms_mview.refresh('mv_sino_person_address','f');end;
ORA-23413:表"SINOJFS"."SINO_PERSON_ADDRESS"没有实体化视图日志
ORA-06512:在"SYS.DBMS_SNAPSHOT",line2558
ORA-06512:在"SYS.DBMS_SNAPSHOT",line2771
ORA-06512:在"SYS.DBMS_SNAPSHOT",line2740
ORA-06512:在line2
SQL>creatematerializedviewlogonsino_person_employment重新创建物化视图日志
2withprimarykey
3includingnewvalues;
Materializedviewlogcreated
SQL>execdbms_mview.refresh('mv_sino_person_employment','f');但还是不支持增量刷新，因为日志内容和原表内容不一致了
begindbms_mview.refresh('mv_sino_person_employment','f');end;
ORA-12034:"SINOJFS"."SINO_PERSON_EMPLOYMENT"上的实体化视图日志比上次刷新后的内容新
ORA-06512:在"SYS.DBMS_SNAPSHOT",line2558
ORA-06512:在"SYS.DBMS_SNAPSHOT",line2771
ORA-06512:在"SYS.DBMS_SNAPSHOT",line2740
ORA-06512:在line2
因为：丢失了删除日志那一点->重建日志那一点之间的原表DML变化，因此日志内容和原表内容不一致了
解决方案：重建物化视图日志重新【增量刷新】和【全表刷新】一遍
SQL>dropmaterializedviewlogonsino_loan_compact;删除日志
Materializedviewlogdropped
SQL>creatematerializedviewlogonsino_loan_compact重建日志
2withprimarykey
3includingnewvalues;
Materializedviewlogcreated
SQL>execdbms_mview.refresh('mv_sino_loan_compact','c');必须先全表刷新
PL/SQLproceduresuccessfullycompleted
SQL>execdbms_mview.refresh('mv_sino_loan_compact','f');再增量刷新，否则ORA-12034:"SINOJFS"."SINO_LOAN_COMPACT"上的实体化视图日志比上次刷新后的内容新
PL/SQLproceduresuccessfullycompleted
小结：只要能够增量刷新，说明日志没有问题了
简述所有视图的快速刷新和全表刷新命令（测试使用）10张视图
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完成完成完成完成
对比IPBCSTATE字段基表有，但物化视图没有，需要重建物化视图解决
select*frommv_sino_loan_compactwhererownum<2;
select*fromsino_loan_compactwhererownum<2;
select*frommv_sino_loanwhererownum<2;
select*fromsino_loanwhererownum<2;
select*frommv_sino_loan_applywhererownum<2;
select*fromsino_loan_applywhererownum<2;
select*frommv_sino_loan_guaranteewhererownum<2;
select*fromsino_loan_guaranteewhererownum<2;
select*frommv_sino_loan_guaranteewhererownum<2;
select*fromsino_loan_guaranteewhererownum<2;
select*frommv_sino_loan_investorwhererownum<2;
select*fromsino_loan_investorwhererownum<2;
select*frommv_sino_loan_spec_tradewhererownum<2;
select*fromsino_loan_spec_tradewhererownum<2;
################################################################################
select*frommv_sino_personwhererownum<2;
select*fromsino_personwhererownum<2;
select*frommv_sino_person_addresswhererownum<2;
select*fromsino_person_addresswhererownum<2;
select*frommv_sino_person_certificationwhererownum<2;
select*fromsino_person_certificationwhererownum<2;
select*frommv_sino_person_employmentwhererownum<2;
select*fromsino_person_employmentwhererownum<2;
##################################################################################
4.因为上面写的物化视图是基于主键进行刷新的，因此原表必须要有主键
6.定时刷新JOB
确定执行时间间隔
1)、每分钟执行
Interval=>TRUNC(sysdate,'mi')+1/(24*60)
2)、每天定时执行
例如：每天下午2点执行一次pro_mview_refresh存储过程
Interval=>TRUNC(sysdate)+1+14/(24)
3)、每周定时执行
例如：每周一凌晨2点执行
Interval=>TRUNC(next_day(sysdate,2))+2/24--星期一,一周的第二天
4)、每月定时执行
例如：每月1日凌晨2点执行
Interval=>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5)、每季度定时执行
例如每季度的第一天凌晨2点执行
Interval=>TRUNC(ADD_MONTHS(SYSDATE,3),'Q')+2/24
6)、每半年定时执行
例如：每年7月1日和1月1日凌晨2点
Interval=>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7)、每年定时执行
例如：每年1月1日凌晨2点执行
Interval=>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
通过jobs的使用就能实现每天或每月的指定时间执行一个函数、过程与命令
setserveroutputon启动屏幕输出功能
SQL>executedbms_output.put_line('Thisis');已经可以正常输出
Thisis
创建作业
variablejob_numnumber;定义存储job编号的变量
declarejob_numnumber;pro_refresh_all_mviews
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'sysdate+1/1440');每天1440分钟，每一分钟运行pro_mview_refresh过程一次
dbms_output.put_line('JobNumberis'||to_char(job_num));
commit;
end;
/
############################################################################################
绑定变量版，必须先定义变量
variablejob_numnumber;
declarejob_numnumber;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE+5/1440,''MI'')');每5分钟运行一次job
dbms_output.put_line('JobNumberis'||to_char(job_num));
commit;
end;
/
例如：每天上午10点执行一次pro_refresh_all_mviews存储过程
Interval=>TRUNC(sysdate)+1+10/(24)
declarejob_numnumber;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24');每天上午10点运行一次job
dbms_output.put_line('JobNumberis'||to_char(job_num));
commit;
end;
/
JobNumberis
PL/SQLproceduresuccessfullycompleted
job_num
---------
1
####################################################################################
PL/SQL匿名块版，可以直接在块中定义变量，比较方面现在采用这种
declare
job_numnumber;
begin
dbms_job.submit
(job=>job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24');
dbms_output.put_line('JobNumberis'||job_num);
commit;
end;
/
JobNumberis4
PL/SQLproceduresuccessfullycompleted
####################################################################################
dbms_job.submit(joboutbinary_integer,
what in varchar2,
next_date in date，
interval in varchar2,
no_parse in boolean)
●job：输出变量，这是作业在作业队列中的编号；
●what：执行作业的存储过程及其输入参数；
●next_date：作业初次执行的时间；
●interval：作业执行的时间间隔。指上一次执行结束到下一次开始执行的时间间隔
其中Interval这个值是决定Job何时，被重新执行的关键；当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。如何更好地确定执行时间的间隔需要我们掌握一个函数TRUNC。
SQL>showparameterjob_queue_process作业队列进程数，oracle能够并发job数量，0~1000
NAMETYPEVALUE
-----------------------------------------------------------------------------
job_queue_processesinteger1000
Oracle提供的数据字典user_jobs监控作业状态
SQL>selectjob,log_user,what,last_date,last_sec,next_date,next_sec,failures,brokenfromuser_jobs;
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
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.remove(:job_num);
end;
修改作业
dbms_job.remove(jobno);删除job号
例executedbms_job.remove(1);
######################################################################
dbms_job.what(jobno,what);修改执行的存储过程
dbms_job.next_date(job,next_date)修改下次执行的时间
例execdbms_job.next_date(46,sysdate+2/(24*60));46作业号
#####################################################################
dbms_job.interval(job,interval):修改间隔时间
例execdbms_job.interval(46,sysdate+3/(24*60));
######################################################################
dbms_job.broken(job,true)中断job
例execdbms_job.broken(46,true);46作业号execdbms_job.broken(2,true)BROKEN=Y
#######################################################################
dbms_job.broken(job,false,next_date)next_date:下次执行时间，如果不填则马上启动job
例execdbms_job.broken(46,false);启动jobexecdbms_job.broken(2,false);BROKEN=N
########################################################################
dbms_job.run(jobno);运行作业
例子executedbms_job.run(1);
http://down.51cto.com/data/945527请点击下载
本文转自 ztfriend 51CTO博客，原文链接：http://blog.51cto.com/leonarding/1297465，如需转载请自行联系原作者