物化视图常用于同步远端数据到本地,同样也适用创建本地表的物化视图(应该很少用)。
#####创建物化视图相关语法
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