《Oracle物化視圖實(shí)戰(zhàn)手冊》
場合：數(shù)據(jù)變化小，查詢出數(shù)據(jù)還要2次利用，需要數(shù)據(jù)雙向同步的場合
視圖：就是一條sql語句，每次查詢時(shí)都要重新生成執(zhí)行計(jì)劃，重新執(zhí)行，非常消耗時(shí)間，放在內(nèi)存中一次性的
物化視圖：執(zhí)行sql并保留結(jié)果，直接放在數(shù)據(jù)文件中，不放在內(nèi)存中方便重用【空間換時(shí)間】，不受開關(guān)機(jī)的影響
1.創(chuàng)建基表并插入數(shù)據(jù)
create table sino_person_address
(
iid NUMBER(16) not null,
ipersonid NUMBER(16),
spin NUMBER(16),
dgettime DATE,
sorgcode VARCHAR2(20),
smsgfilename VARCHAR2(20),
ilineno NUMBER(8),
saddress VARCHAR2(60),
szip CHAR(6),
scondition CHAR(1),
itrust NUMBER(1),
stoporgcode VARCHAR2(14),
istate NUMBER(1),
constraint PK_SINO_PERSON_ADDRESS primary key (iid)
);
插入數(shù)據(jù)(插入自動(dòng)增長序列號字段的方法)
INSERT 語句插入這個(gè)字段值為: 序列號的名稱.NEXTVAL,seq_sino_person_address.nextval
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-08 12:12:12','yyyy-mm-dd hh24:mi:ss'),'110','test_report',111,'beijing
xicheng','100100','1',123,1,'1000',0);
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-09 12:12:12','yyyy-mm-dd hh24:mi:ss'),'120','test_report2',121,'beijing
xicheng','100200','2',123,1,'1002',2);
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-10 12:12:12','yyyy-mm-dd hh24:mi:ss'),'130','test_report3',131,'beijing
xicheng','100300','3',123,1,'1003',3);
commit
###################################################################################################
2.創(chuàng)建物化視圖日志
意義：記錄基表DML操作的變化，實(shí)時(shí)刷新物化視圖
注：包含所有字段
刪除物化視圖日志
drop materialized view log on t
創(chuàng)建物化視圖日志
create materialized view log on t with sequence,rowid (x,y,z) including new values;
參數(shù)說明：
with sequence：以序號增1的方式進(jìn)行變化記錄
rowid (x,y,z)：定位哪些數(shù)據(jù)發(fā)生了變化，日志記錄rowid指向的數(shù)據(jù)塊的位置和變化
刪除物化視圖日志
drop materialized view log on sino_person_address;
基于主鍵方式的刷新，創(chuàng)建物化視圖日志
CREATE MATERIALIZED VIEW LOG ON sino_person_address
WITH PRIMARY KEY
INCLUDING NEW VALUES
【TABLESPACE sinojfs2】; 可選項(xiàng)
3.創(chuàng)建物化視圖
創(chuàng)建物化視圖
create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;
刪除物化視圖
drop materialized view mv_sino_person_address;
create materialized view mv_sino_person_address
tablespace SINOJFS2
build immediate 創(chuàng)建物化視圖時(shí)，立即刷新基表
refresh fast with primary key 支持基于主鍵的快速刷新（增量刷新），基表必須有主鍵
on commit 支持commit動(dòng)作自動(dòng)刷新
enable query rewrite
as select * from sino_person_address;
create materialized view mv_sino_person_address
tablespace SINOJFS2
build immediate
refresh fast with primary key refresh complete全部刷新【全表刷新】可選項(xiàng)
on demand 支持需求時(shí)手工刷新
enable query rewrite
as select * from sino_person_address;
########################################################################################
參數(shù)說明：
build immediate：創(chuàng)建物化視圖時(shí)，立即刷新基表
refresh fast with primary key：支持基于主鍵的快速刷新（增量刷新），基表必須有主鍵
on commit：基于commit動(dòng)作的自動(dòng)刷新 on demand：基于需求時(shí)的手工刷新
enable query rewrite：支持查詢重新（使用物化視圖代替基表，查詢必須重寫，查詢重寫是透明的并且不需要對物化視圖有任何權(quán)限，物化視圖可以啟用和禁用查詢重寫）
查詢重寫：select * from t基表，執(zhí)行計(jì)劃走的是mv_t物化視圖，禁用后，執(zhí)行計(jì)劃走的就是t基表了
tablespace SINOJFS2 創(chuàng)建于SINOJFS2表空間
(1)創(chuàng)建方式：BUILD IMMEDIATE（立即生成數(shù)據(jù)）， BUILD DEFERRED（下一次刷新時(shí)生新數(shù)據(jù)）， ON PREBUILD TABLE（不創(chuàng)建新的數(shù)據(jù)段，用已存在的含有當(dāng)前物化視圖數(shù)據(jù)的表來代替）；
(2)ENABLE | DISABLE QUERY REWRITE指定是否啟用當(dāng)前物化視圖用于查詢重寫，啟用該選項(xiàng)時(shí)，系統(tǒng)會檢查以保證查詢的可確定性（不允許有如序列數(shù)，USER， DATE等不確定的返回值），DISABLE時(shí)物化視圖照樣可以被刷新；
與物化視圖生效相關(guān)的設(shè)置
(1)初始化參數(shù)JOB_QUEUE_PROCESSES設(shè)置大于零，物化的自動(dòng)刷新操作需要JOB QUEUE進(jìn)程來執(zhí)行；
(2)初始化參數(shù)OPTIMIZER_MODE要設(shè)成某種CBO優(yōu)化模式；
(3)用戶會話有QUERY_REWRITE（優(yōu)化器能將查詢重寫到本方案物化視圖）或GLOBAL_QUERY_REWRITE（優(yōu)化器能將查詢重寫到其它方案的物化視圖）系統(tǒng)權(quán)限；
(4)初始化參數(shù)QUERY_REWRITE_ENABLED 指示優(yōu)化器是否動(dòng)態(tài)重寫查詢來使用物化視圖，這個(gè)參數(shù)可以在四個(gè)級別上進(jìn)行設(shè)置（參數(shù)文件，ALTER SYSTEM， ALTER SESSION， HINTS）；
(5)初始化參數(shù)QUERY_REWRITE_INTEGRITY 指示優(yōu)化器在不同的數(shù)據(jù)一致性情況下決定是否使用物化視圖來重寫查詢，ENFORCED（只有在能確保數(shù)據(jù)一致的前提下才使用物化視圖）， TRUSTED（數(shù)據(jù)不一定一致，只要有用維度對象定義的關(guān)系存在，就可使用物化視圖）， STALE_TOLERATED（數(shù)據(jù)不一致，也沒有相關(guān)的維度定義時(shí)仍可使用物化視圖），這個(gè)參數(shù)可以在三個(gè)級別上進(jìn)行設(shè)置（參數(shù)文件，ALTER SYSTEM， ALTER SESSION）；
4. 物化視圖DML操作測試
（1）驗(yàn)證物化視圖是否隨記錄增加而增加
insert into sino_person_address values(seq_sino_person_address.nextval,123,to_date('2013-04-11 13:13:13','yyyy-mm-dd hh24:mi:ss'),'140','test_report4',141,'beijing
xicheng','100400','4',123,1,'1004',4);
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 隨記錄增加而木有刷新，必須commit之后才觸發(fā)物化視圖刷新，沒有問題
exec dbms_mview.refresh('mv_sino_person_address','c'); 還可以手動(dòng)全部刷新【全表刷新】（先清除，再重裝數(shù)據(jù)）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來進(jìn)行刷新）
（2）驗(yàn)證物化視圖是否隨記錄刪除而減少
delete from sino_person_address where iid=21;
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 隨記錄刪除而木有刷新，必須commit之后才觸發(fā)物化視圖刷新，沒有問題
exec dbms_mview.refresh('mv_sino_person_address','c'); 還可以手動(dòng)全部刷新【全表刷新】（先清除，再重裝數(shù)據(jù)）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來進(jìn)行刷新）
（3）驗(yàn)證物化視圖是否隨記錄修改而更新
update sino_person_address set sorgcode='200' where sorgcode='120';
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 隨記錄修改而木有刷新，必須commit之后才觸發(fā)物化視圖刷新，沒有問題
exec dbms_mview.refresh('mv_sino_person_address','c'); 還可以手動(dòng)全部刷新【全表刷新】（先清除，再重裝數(shù)據(jù)）
exec dbms_mview.refresh('mv_sino_person_address','f'); 也可以快速刷新【增量刷新】（借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來進(jìn)行刷新）
（4）驗(yàn)證物化視圖是否隨truncate而清空
truncate table sino_person_address;
select * from sino_person_address order by dgetdate;
select * from mv_sino_person_address order by dgetdate; 隨記錄truncate而木有清空，必須手動(dòng)truncate table mv_sino_person_address;才能清空（兩者是沒有關(guān)聯(lián)的），沒有問題
5.物化視圖刷新
根據(jù)業(yè)務(wù)需求，每月定時(shí)刷新。根據(jù)以上條件，選擇使用ORACLE自帶工具DBMS_MVIEW工具包中REFRESH方法對物化視圖進(jìn)行刷新。該方法有兩個(gè)參數(shù)，第一個(gè)參數(shù)是需要刷新的物化視圖名稱，第二個(gè)參數(shù)是刷新方式。我們可以寫存儲過程，對每個(gè)物化視圖調(diào)用一次REFRESH方法，也可以使用“，”把物化視圖連接以來，一次刷新。
定義存儲過程
create or replace procedure pro_mview_refresh
as
begin
dbms_mview.refresh('mv_sino_person_address','f');
end;
/
執(zhí)行存儲過程
execute pro_mview_refresh;
還可以刷新所有物化視圖 dbms_mview.refresh_all_mviews;
創(chuàng)建存儲過程
drop procedure pro_refresh_all_mviews;
create or replace procedure pro_refresh_all_mviews
as
i number;
begin
dbms_mview.refresh_all_mviews(number_of_failures=>i);
dbms_output.put_line('number_of_failures=>'||i);
end;
/
執(zhí)行
executepro_refresh_all_mviews;
set serveroutput on;不可放在存儲過程中，因?yàn)檫@是sqlplus命令，如果你怕忘記或者嫌麻煩可以把set serveroutput on;
寫入/opt/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql中，每次使用sqlplus時(shí)自動(dòng)加載這個(gè)文件
如果想用PL/SQL Developer工具訪問數(shù)據(jù)庫，請?jiān)贑:\Program Files\PLSQL Developer\Login.sql 文件里添加
-- Autostart Command Window script
set serveroutput on;
這樣以后再使用PL/SQL Developer工具訪問數(shù)據(jù)庫就可以自動(dòng)加載這條命令了
###############################################################################################
研發(fā)人員專用，手動(dòng)刷新，想刷就刷
set serveroutput on; 打開屏幕顯示功能，就可以看到number_of_failures=>0結(jié)果
PL/SQL 匿名塊
declare
i number;
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 表示刷新物化視圖失敗個(gè)數(shù)
采用默認(rèn)refresh force 刷新方式：先試圖用FAST方式刷新，如果失敗再用COMPLETE方式刷新，這是默認(rèn)的刷新方式
注意:
1、 如果需要同時(shí)刷新多個(gè)物化視圖，必須用逗號把各個(gè)物化視圖名稱連接起來，并對每個(gè)視圖都要指明刷新方式（f、增量刷新，c、完全刷新，?、強(qiáng)制刷新，從不刷新）。
NEVER REFRESH（不刷新）
REFREST FAST（借助物化視圖日志，只檢查自上次刷新后改變了的數(shù)據(jù)來進(jìn)行刷新）
REFRESH COMPLETE（先清除，再重裝數(shù)據(jù)）
REFRESH FORCE（先試圖用FAST方式刷新，如果失敗再用COMPLETE方式刷新，這是默認(rèn)的刷新方式）
確定刷新時(shí)機(jī)：
ON COMMIT（事務(wù)提交時(shí)刷新），
ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS來手工刷新），
By Time（用START WITH 和 NEXT 子句創(chuàng)建的job來定時(shí)自動(dòng)刷新）；
[dbms_mview.refresh('mv_sino_person_address,mv_person_address_his','ff');]
2、當(dāng)日志和物化視圖創(chuàng)建好后，刪除日志，則需要重新創(chuàng)建物化視圖，否則無法增量刷新。
drop materialized view log on sino_person_address; 刪除日志
SQL> exec dbms_mview.refresh('mv_sino_person_address','c'); 刪除物化視圖日志，只可以支持物化視圖全部刷新
PL/SQL procedure successfully completed
#################################################################################
SQL> exec dbms_mview.refresh('mv_sino_person_address','f'); 無法增量刷新
begin dbms_mview.refresh('mv_sino_person_address','f'); end;
ORA-23413: 表 "SINOJFS"."SINO_PERSON_ADDRESS" 沒有實(shí)體化視圖日志
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 2
SQL> create materialized view log on sino_person_employment 重新創(chuàng)建物化視圖日志
2 with primary key
3 including new values;
Materialized view log created
SQL> exec dbms_mview.refresh('mv_sino_person_employment','f'); 但還是不支持增量刷新，因?yàn)槿罩緝?nèi)容和原表內(nèi)容不一致了
begin dbms_mview.refresh('mv_sino_person_employment','f'); end;
ORA-12034: "SINOJFS"."SINO_PERSON_EMPLOYMENT" 上的實(shí)體化視圖日志比上次刷新后的內(nèi)容新
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 2
因?yàn)椋簛G失了刪除日志那一點(diǎn)->重建日志那一點(diǎn)之間的原表DML變化，因此日志內(nèi)容和原表內(nèi)容不一致了
解決方案：重建物化視圖日志 重新【增量刷新】和【全表刷新】一遍
SQL> drop materialized view log on sino_loan_compact; 刪除日志
Materialized view log dropped
SQL> create materialized view log on sino_loan_compact 重建日志
2 with primary key
3 including new values;
Materialized view log created
SQL> exec dbms_mview.refresh('mv_sino_loan_compact','c'); 必須先全表刷新
PL/SQL procedure successfully completed
SQL> exec dbms_mview.refresh('mv_sino_loan_compact','f'); 再增量刷新，否則ORA-12034: "SINOJFS"."SINO_LOAN_COMPACT" 上的實(shí)體化視圖日志比上次刷新后的內(nèi)容新
PL/SQL procedure successfully completed
小結(jié)：只要能夠增量刷新，說明日志沒有問題了
簡述所有視圖的快速刷新和全表刷新命令（測試使用） 10張視圖
select owner,table_name,tablespace_name,status from dba_tables where table_name in ('SINO_LOAN_APPLY');
update SINO_LOAN_APPLY set sorgcode ='1000' where iid =858;
exec dbms_mview.refresh('mv_sino_loan_compact','c');
exec dbms_mview.refresh('mv_sino_loan_compact','f');
exec dbms_mview.refresh('mv_sino_loan_apply','c');
exec dbms_mview.refresh('mv_sino_loan_apply','f');
exec dbms_mview.refresh('mv_sino_loan_spec_trade','c');
exec dbms_mview.refresh('mv_sino_loan_spec_trade','f');
exec dbms_mview.refresh('mv_sino_loan','c');
exec dbms_mview.refresh('mv_sino_loan','f');
exec dbms_mview.refresh('mv_sino_loan_guarantee','c');
exec dbms_mview.refresh('mv_sino_loan_guarantee','f');
exec dbms_mview.refresh('mv_sino_loan_investor','c');
exec dbms_mview.refresh('mv_sino_loan_investor','f');
###############################################################################
exec dbms_mview.refresh('mv_sino_person_employment','c');
exec dbms_mview.refresh('mv_sino_person_employment','f');
exec dbms_mview.refresh('mv_sino_person_address','c');
exec dbms_mview.refresh('mv_sino_person_address','f');
exec dbms_mview.refresh('mv_sino_person_certification','c');
exec dbms_mview.refresh('mv_sino_person_certification','f');
exec dbms_mview.refresh('mv_sino_person','c');
exec dbms_mview.refresh('mv_sino_person','f');
3.基表增加字段后對應(yīng)物化視圖不能自動(dòng)同步結(jié)構(gòu)
業(yè)務(wù)表增加 上報(bào)狀態(tài) 字段 ipbcstate number(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 * from mv_sino_loan_compact where rownum<2;
select * from sino_loan_compact where rownum<2;
select * from mv_sino_loan where rownum < 2;
select * from sino_loan where rownum < 2;
select * from mv_sino_loan_apply where rownum < 2;
select * from sino_loan_apply where rownum < 2;
select * from mv_sino_loan_guarantee where rownum < 2;
select * from sino_loan_guarantee where rownum < 2;
select * from mv_sino_loan_guarantee where rownum < 2;
select * from sino_loan_guarantee where rownum < 2;
select * from mv_sino_loan_investor where rownum < 2;
select * from sino_loan_investor where rownum < 2;
select * from mv_sino_loan_spec_trade where rownum < 2;
select * from sino_loan_spec_trade where rownum < 2;
################################################################################
select * from mv_sino_person where rownum < 2;
select * from sino_person where rownum < 2;
select * from mv_sino_person_address where rownum < 2;
select * from sino_person_address where rownum < 2;
select * from mv_sino_person_certification where rownum < 2;
select * from sino_person_certification where rownum < 2;
select * from mv_sino_person_employment where rownum < 2;
select * from sino_person_employment where rownum < 2;
##################################################################################
4.因?yàn)樯厦鎸懙奈锘晥D是基于主鍵進(jìn)行刷新的，因此原表必須要有主鍵
6.定時(shí)刷新JOB
確定執(zhí)行時(shí)間間隔
1)、 每分鐘執(zhí)行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)
2)、 每天定時(shí)執(zhí)行
例如：每天下午2點(diǎn)執(zhí)行一次pro_mview_refresh存儲過程
Interval => TRUNC(sysdate) + 1 +14/ (24)
3)、 每周定時(shí)執(zhí)行
例如：每周一凌晨2點(diǎn)執(zhí)行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天
4)、 每月定時(shí)執(zhí)行
例如：每月1日凌晨2點(diǎn)執(zhí)行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24
5)、 每季度定時(shí)執(zhí)行
例如每季度的第一天凌晨2點(diǎn)執(zhí)行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24
6)、 每半年定時(shí)執(zhí)行
例如：每年7月1日和1月1日凌晨2點(diǎn)
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24
7)、 每年定時(shí)執(zhí)行
例如：每年1月1日凌晨2點(diǎn)執(zhí)行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24
通過jobs的使用就能實(shí)現(xiàn)每天或每月的指定時(shí)間執(zhí)行一個(gè)函數(shù)、過程與命令
set serveroutput on 啟動(dòng)屏幕輸出功能
SQL> execute dbms_output.put_line('This is'); 已經(jīng)可以正常輸出
This is
創(chuàng)建作業(yè)
variable job_num number; 定義存儲job編號的變量
declare job_num number; pro_refresh_all_mviews
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'sysdate+1/1440'); 每天1440分鐘，每一分鐘運(yùn)行pro_mview_refresh過程一次
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
############################################################################################
綁定變量版，必須先定義變量
variable job_num number;
declare job_num number;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE+5/1440,''MI'')'); 每5分鐘運(yùn)行一次job
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
例如：每天上午10點(diǎn)執(zhí)行一次pro_refresh_all_mviews存儲過程
Interval => TRUNC(sysdate) + 1 +10/ (24)
declare job_num number;
begin
dbms_job.submit
(job=>:job_num,
what=>'pro_refresh_all_mviews;',
next_date=>sysdate,
interval=>'trunc(SYSDATE)+1+10/24'); 每天上午10點(diǎn)運(yùn)行一次job
dbms_output.put_line('Job Number is'||to_char(job_num));
commit;
end;
/
Job Number is
PL/SQL procedure successfully completed
job_num
---------
1
####################################################################################
PL/SQL 匿名塊版，可以直接在塊中定義變量，比較方面現(xiàn)在采用這種
declare
job_num number;
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('Job Number is '||job_num);
commit;
end;
/
Job Number is 4
PL/SQL procedure successfully completed
####################################################################################
dbms_job.submit( job out binary_integer,
what in varchar2,
next_date in date，
interval in varchar2,
no_parse in boolean)
●job：輸出變量，這是作業(yè)在作業(yè)隊(duì)列中的編號；
●what：執(zhí)行作業(yè)的存儲過程及其輸入?yún)?shù)；
●next_date：作業(yè)初次執(zhí)行的時(shí)間；
●interval：作業(yè)執(zhí)行的時(shí)間間隔。指上一次執(zhí)行結(jié)束到下一次開始執(zhí)行的時(shí)間間隔
其中Interval這個(gè)值是決定Job何時(shí)，被重新執(zhí)行的關(guān)鍵；當(dāng)interval設(shè)置為null時(shí)，該job執(zhí)行結(jié)束后，就被從隊(duì)列中刪除。假如我們需要該job周期性地執(zhí)行，則要用‘sysdate＋m’表示。如何更好地確定執(zhí)行時(shí)間的間隔需要我們掌握一個(gè)函數(shù)TRUNC。
SQL> show parameter job_queue_process 作業(yè)隊(duì)列進(jìn)程數(shù)，oracle能夠并發(fā)job數(shù)量，0~1000
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
Oracle提供的數(shù)據(jù)字典user_jobs監(jiān)控作業(yè)狀態(tài)
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;
Job 作業(yè)唯一編號
Log_user 提交作業(yè)的用戶
What 作業(yè)執(zhí)行的存儲過程
Last_date 最后一次成功運(yùn)行作業(yè)的日期
Last_sec 最后一次成功運(yùn)行作業(yè)的時(shí)間
Next_date 下一次運(yùn)行作業(yè)日期
Next_sec 下一次運(yùn)行作業(yè)時(shí)間
Failures 執(zhí)行失敗次數(shù)，當(dāng)執(zhí)行job出現(xiàn)錯(cuò)誤時(shí)，Oracle將其記錄在日志里，失敗次數(shù)每次自動(dòng)加1，加到16之后Oracle就不在執(zhí)行它了
Broken 是否是異常作業(yè)，當(dāng)執(zhí)行失敗次數(shù)達(dá)到16時(shí)，Oracle就將該job標(biāo)志為broken。此后，Oracle不再繼續(xù)執(zhí)行它，直到用戶調(diào)用過程dbms_job.broken，重新設(shè)置為not broken，或強(qiáng)制調(diào)用dbms_job.run來重新執(zhí)行它。Y標(biāo)示作業(yè)中斷，以后不會運(yùn)行，N表示作業(yè)正常，可以運(yùn)行
運(yùn)行作業(yè)
begin
dbms_job.run(:job_num); job_num是存儲job編號的變量
end;
查詢作業(yè)狀態(tài)
SQL> select job,log_user,what,last_date,last_sec,next_date,next_sec,failures,broken from user_jobs;
JOB LOG_USER WHAT LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- --------- ---------------------------------------------------- ----------- ---------------- -----------
1 SINOJFS pro_refresh_all_mviews; 2013-4-26 1 11:27:38 2013-4-27 1 10:00:00 0 N
Job 作業(yè)唯一編號
Log_user 提交作業(yè)的用戶
What 作業(yè)執(zhí)行的存儲過程
Last_date 最后一次成功運(yùn)行作業(yè)的日期
Last_sec 最后一次成功運(yùn)行作業(yè)的時(shí)間
Next_date 下一次運(yùn)行作業(yè)日期
Next_sec 下一次運(yùn)行作業(yè)時(shí)間
Failures 執(zhí)行失敗次數(shù)，當(dāng)執(zhí)行job出現(xiàn)錯(cuò)誤時(shí)，Oracle將其記錄在日志里，失敗次數(shù)每次自動(dòng)加1，加到16之后Oracle就不在執(zhí)行它了
Broken 是否是異常作業(yè)，當(dāng)執(zhí)行失敗次數(shù)達(dá)到16時(shí)，Oracle就將該job標(biāo)志為broken。此后，Oracle不再繼續(xù)執(zhí)行它，直到用戶調(diào)用過程dbms_job.broken，重新設(shè)置為not broken；
或強(qiáng)制調(diào)用dbms_job.run來重新執(zhí)行它。Y標(biāo)示作業(yè)中斷，以后不會運(yùn)行，N表示作業(yè)正常，可以運(yùn)行
刪除作業(yè)
begin
dbms_job.remove(:job_num);
end;
修改作業(yè)
dbms_job.remove(jobno); 刪除job號
例 execute dbms_job.remove(1);
######################################################################
dbms_job.what(jobno,what); 修改執(zhí)行的存儲過程
dbms_job.next_date(job,next_date)修改下次執(zhí)行的時(shí)間
例 exec dbms_job.next_date(46,sysdate+2/(24*60)); 46作業(yè)號
#####################################################################
dbms_job.interval(job,interval) :修改間隔時(shí)間
例 exec dbms_job.interval(46,sysdate+3/(24*60));
######################################################################
dbms_job.broken(job,true) 中斷job
例 exec dbms_job.broken(46,true); 46作業(yè)號 exec dbms_job.broken(2,true) BROKEN=Y
#######################################################################
dbms_job.broken(job,false,next_date) next_date:下次執(zhí)行時(shí)間，如果不填則馬上啟動(dòng)job
例 exec dbms_job.broken(46,false); 啟動(dòng)job exec dbms_job.broken(2,false); BROKEN=N
########################################################################
dbms_job.run(jobno); 運(yùn)行作業(yè)
例子 execute dbms_job.run(1);