如何在每天下午5点刷新的Oracle EBS 12.2中创建物化视图

Question

我正试图在经过编辑的Oracle电子商务套件12.2安装上的11g Oracle数据库中的xxcus自定义模式中创建一个物化视图。我想通过xxcus模式上的OracleApex安装以及数据库链接访问这个物化视图。Oracle建议数据库链接使用自己的模式xxcus_ro。

使用EBS，您必须根据Oracle支持文档ID 1577661.1以特殊方式创建物化视图。首先创建一个视图：

create or replace view apps.xxar_invoice_totals_mv# as       
select rctv.trx_number invoice_number,
rctv.rac_bill_to_customer_name,
rctv.rac_bill_to_customer_num,
sum(rctla.extended_amount) invoice_total
from   apps.ra_customer_trx_v       rctv,
ar.ra_customer_trx_lines_all rctla
where  rctla.customer_trx_id = rctv.customer_trx_id
group  by rctv.trx_number,
rctv.rac_bill_to_customer_name,
rctv.rac_bill_to_customer_num;

然后运行exec ad_zd_mview.upgrade('APPS', 'XXAR_INVOICE_TOTALS_MV')以Oracle EBS希望的方式创建物化视图。

虽然这可以工作并创建物化视图，但它创建的视图使用REFRESH FORCE ON DEMAND，并且ad_zd_mview.upgrade没有指定刷新间隔的参数。文档ID 1577661.1也没有提及任何关于刷新的内容。

在发现Doc ID 157766.1:之前，我想创建的原始物化视图

create materialized view xxcus.xxar_invoice_totals_mv as
tablespace APPS_TS_TX_DATA
pctused    40
pctfree    10
initrans   2
maxtrans   255
storage    (
initial          40k
next             40k
minextents       1
maxextents       unlimited
pctincrease      0
freelists        1
freelist groups  1
buffer_pool      default
)
nocache
logging
noparallel
build immediate
refresh complete
start with to_date('7-Sep-2020 10:25:00','dd-mon-yyyy hh24:mi:ss')
next to_date(to_char(sysdate+1,'yyyy-mon-dd')||' 20:10','yyyy-mon-dd HH24:MI')
as
select rctv.trx_number invoice_number,
rctv.rac_bill_to_customer_name,
rctv.rac_bill_to_customer_num,
sum(rctla.extended_amount) invoice_total
from   apps.ra_customer_trx_v       rctv,
ar.ra_customer_trx_lines_all rctla
where  rctla.customer_trx_id = rctv.customer_trx_id
group  by rctv.trx_number,
rctv.rac_bill_to_customer_name,
rctv.rac_bill_to_customer_num;

如何在EBS 12.2中创建每天早上5点刷新的物化视图？

Answer 1

试试这个：

ALTER MATERIALIZED VIEW XXAR_INVOICE_TOTALS_MV
REFRESH COMPLETE
NEXT TRUNC(SYSDATE) + 5;

除非你对物化视图没有以下限制：

https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007007