dbms_mview系列(五)_补

最新推荐文章于 2023-09-27 16:13:22 发布

clg10051

最新推荐文章于 2023-09-27 16:13:22 发布

阅读量111

点赞数

--修正method参数值为c,原为f;这下可以正常刷新了
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_all_mviews(number_of_failures => out_err,method => 'c',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /

0

PL/SQL procedure successfully completed

SQL> select * from mv_t;

                                      A
---------------------------------------
                                      1

适用于刷新与master table相关的物化视图
REFRESH_DEPENDENT Procedures

Syntax
DBMS_MVIEW.REFRESH_DEPENDENT (
   number_of_failures     OUT    BINARY_INTEGER,
   { list                 IN     VARCHAR2,     --仅此参数与上述不同,为master table,而非mv
   | tab                  IN     DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2    := NULL,
   rollback_seg           IN     VARCHAR2    := NULL,
   refresh_after_errors   IN     BOOLEAN     := false,
   atomic_refresh         IN     BOOLEAN     := true,
   nested                 IN     BOOLEAN     := false);

--插入数据到master table
SQL> insert into t_mv select level from dual connect by level<=5;

5 rows inserted

SQL> commit;

Commit complete

SQL> select * from mv_t;

                                      A
---------------------------------------

SQL>
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_dependent(number_of_failures => out_err,list=>'mv_t',method => 'f',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /

0

PL/SQL procedure successfully completed

--运行过程物化视图依旧无值
SQL> select * from mv_t;

                                      A
---------------------------------------

--list参数值为master table,而非mv,mv有值了
SQL> declare
2 out_err number;
3 begin
4 dbms_mview.refresh_dependent(number_of_failures => out_err,list=>'t_mv',method => 'c',refresh_after_errors => true,atomic_refresh => false);
5 dbms_output.put_line(out_err);
6 end;
7 /

0

PL/SQL procedure successfully completed

SQL> select * from mv_t;

                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                      4
                                      5