[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. ] 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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值