[2021-08-10]oracle物化视图创建例子和常用关联语句

最新推荐文章于 2024-05-23 06:59:48 发布

qxy0503

最新推荐文章于 2024-05-23 06:59:48 发布

阅读量627

点赞数

分类专栏： oracle 文章标签：物化视图 schedule

本文链接：https://blog.csdn.net/m15217321304/article/details/119571834

版权

oracle 专栏收录该内容

60 篇文章 2 订阅

订阅专栏

物化视图常用于同步远端数据到本地，同样也适用创建本地表的物化视图（应该很少用）。

#####创建物化视图相关语法

CREATE MATERIALIZED VIEW语法

CREATE MATERIALIZED VIEW [ schema. ] materialized_view
[ column_alias [ENCRYPT [encryption_spec]] [, column_alias [ENCRYPT [encryption_spec]] ]... ]
[ OF [ schema. ] object_type ]
[ (scoped_table_ref_constraint) ]
{ ON PREBUILT TABLE [ { WITH | WITHOUT } REDUCED PRECISION ]
| physical_properties materialized_view_props
}
[ USING INDEX [ physical_attributes_clause
| TABLESPACE tablespace
]...
| USING NO INDEX ]
[ create_mv_refresh ]
[ FOR UPDATE ]
[ { DISABLE | ENABLE } QUERY REWRITE ] AS subquery ;

刷新方式
{ REFRESH { { FAST | COMPLETE | FORCE }

  | { ON DEMAND | ON COMMIT }
  | { START WITH date | NEXT date 
    }...
  | WITH { PRIMARY KEY | ROWID }
  | USING { DEFAULT [ MASTER | LOCAL ] ROLLBACK SEGMENT | [ MASTER | LOCAL ] ROLLBACK SEGMENT rollback_segment
     }...
  | USING { ENFORCED | TRUSTED } CONSTRAINTS }...
| NEVER REFRESH }  create_mv_refresh：

CREATE MATERIALIZED VIEW LOG

CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
  [ physical_attributes_clause
  | TABLESPACE tablespace
  | logging_clause
  | { CACHE | NOCACHE }
  ]...
  [ parallel_clause ]
  [ table_partitioning_clauses ]
  [ WITH { OBJECT ID | PRIMARY KEY | ROWID | SEQUENCE | COMMIT SCN | (column [, column ]...)
         }
           [ { , OBJECT ID | , PRIMARY KEY | , ROWID | , SEQUENCE | , COMMIT SCN | (column [, column ]...)
             }
           ]...
    [ new_values_clause ]
  ] [ mv_log_purge_clause ] 
;

#####下面创建本地物化视图(非sys用户)

1、本地创建测试表T1

SQL> create table t1 (id int);

Table created.

SQL> insert into t1 values (10);

1 row created.

SQL> commit;

Commit complete.

SQL>

2、本地创建物化视图日志

SQL> create materialized view log on t1 with rowid including new values;

Materialized view log created.

SQL>
###drop materialized view log on t1;

3、创建物化视图

###如果没有创建物化视图日志，创建快速刷新会报错
SQL> create materialized view t1_mater
  2  build immediate
  3  refresh fast with rowid
  4  on demand
  5  ENABLE QUERY REWRITE
  6  as select * from t1;
as select * from t1
                 *
ERROR at line 6:
ORA-23413: table "SCOTT"."T1" does not have a materialized view log


create materialized view t1_mater
build immediate
refresh fast with rowid
on demand
ENABLE QUERY REWRITE
as select * from t1;

####全量刷新可以
SQL> create materialized view t1_mater
  2  build immediate
  3  refresh with rowid
  4  on demand
  5  ENABLE QUERY REWRITE
  6  as select * from t1;

Materialized view created.

SQL> 
#### drop materialized view t1_mater

4、查询物化视图

SQL> select * from t1_mater;

        ID
----------
        10

SQL>

5、手动刷新

 SQL> insert into t1 values (20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1_mater;

        ID
----------
        10

SQL> BEGIN
  2     DBMS_MVIEW.REFRESH (
  3       list   =>  'scott.t1_mater',
  4       method =>  'f');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t1_mater;

        ID
----------
        10
        20

SQL>

6、自动刷新创建schedule

--10秒刷新一次

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_SZSP_IM_PR_NOGROUP',
job_type => 'PLSQL_BLOCK',
job_action => 'begin DBMS_MVIEW.REFRESH(''scott.t1_mater'',''f'');end;',  --快速刷新f，全量刷新c
start_date        => SYSDATE,
repeat_interval  => 'FREQ=SECONDLY; INTERVAL=10',  --10分钟刷新一次
comments          => 'Refresh materialized scott.t1_mater',
enabled => true);
END;
/

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB (
  3  job_name => 'JOB_SZSP_IM_PR_NOGROUP',
  4  job_type => 'PLSQL_BLOCK',
  5  job_action => 'begin DBMS_MVIEW.REFRESH(''scott.t1_mater'',''f'');end;',  --快速刷新f，全量刷新c
  6  start_date        => SYSDATE,
  7  repeat_interval  => 'FREQ=SECONDLY; INTERVAL=10',
  8  comments          => 'Refresh materialized scott.t1_mater',
  9  enabled => true);
10  END;
11  /
SQL> insert into t1 values (30);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1_mater;

        ID
----------
        10
        20

SQL> /

        ID
----------
        10
        20
        30

SQL> /

7、查询物化视图执行情况

set line 200
col owner for a30
col JOB_NAME for a30
col LOG_DATE for a50
col STATUS for a10
col OPERATION for a5
select OWNER,JOB_NAME,LOG_DATE,STATUS,ERROR# from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='JOB_SZSP_IM_PR_NOGROUP' and owner='SCOTT' order by LOG_DATE;

set line 200
col owner for a30
col JOB_NAME for a30
col LOG_DATE for a50
col STATUS for a10
col OPERATION for a5
select OWNER,JOB_NAME,LOG_DATE,STATUS,OPERATION,ADDITIONAL_INFO from dba_scheduler_job_log where job_name='JOB_SZSP_IM_PR_NOGROUP' order by LOG_DATE;

SQL> set line 200
SQL> col owner for a30
SQL> col JOB_NAME for a30
SQL> col LOG_DATE for a50
SQL> col STATUS for a10
SQL> col OPERATION for a5
SQL> select OWNER,JOB_NAME,LOG_DATE,STATUS,ERROR# from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='JOB_SZSP_IM_PR_NOGROUP' and owner='SCOTT' order by LOG_DATE;

OWNER                          JOB_NAME                       LOG_DATE                                           STATUS         ERROR#
------------------------------ ------------------------------ -------------------------------------------------- ---------- ----------
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.37.28.600326 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.37.38.034102 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.37.48.035817 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.37.58.035922 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.38.08.143531 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.38.18.078656 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.38.28.027562 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.38.38.231782 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.38.48.062023 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.38.58.032071 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.39.08.032295 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.39.18.029698 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.39.28.042761 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.39.38.036945 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.39.48.043301 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.39.58.025722 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.40.08.037179 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.40.18.028726 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.40.28.032303 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.40.38.039850 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.40.48.023475 PM +08:00                SUCCEEDED           0
SCOTT                           JOB_SZSP_IM_PR_NOGROUP         10-AUG-21 03.40.58.039564 PM +08:00                SUCCEEDED           0

qxy0503

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
[2021-08-10]oracle物化视图创建例子和常用关联语句

物化视图常用于同步远端数据到本地，同样也适用创建本地表的物化视图（应该很少用）。#####创建物化视图相关语法CREATE MATERIALIZED VIEW语法CREATE MATERIALIZED VIEW [ schema. ] materialized_view [ column_alias [ENCRYPT [encryption_spec]] [, column_alias [ENCRYPT [encryption_spec]] ]... ] [ OF[ schema. ...
复制链接

扫一扫