【SQL杂货铺】-oracle视图和物化视图

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

set line 1000 pagesize 1000

Col owner for a15

col view_name for a25

Col read_only for a10

select owner,view_name,text,read_only from dba_views where owner='RFUSER';

--1、创建物化视图需要的权限

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

--2、在源表建立物化视图日志

CREATE MATERIALIZED VIEW LOG ON DAVE

TABLESPACE  &BISONCU_SPACE              -- 日志空间

WITH PRIMARY KEY;                       -- 指定为主键类型

--3、授权给中间用户

GRANT SELECT ON DAVE TO ANQING;

GRANT SELECT ON MLOG$_DAVE TO ANQING;

--4、在目标数据库上创建MATERIALIZED VIEW

CREATE MATERIALIZED VIEW AICS_DAVE TABLESPACE &BISONCS_SPACE  

REFRESH FAST  

   ON DEMAND  

   --第一次刷新时间  

   --START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')  

   START WITH SYSDATE  

   --刷新时间间隔。每1天刷新一次，时间为凌晨2点  

   --NEXT TRUNC(SYSDATE,'dd')+1+2/24  

   NEXT SYSDATE+1/24/20  

WITH PRIMARY KEY  

--USING DEFAULT LOCAL ROLLBACKSEGMENT  

DISABLE QUERY REWRITE AS  

SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION  FROM AICS_DAVE@LINK_DAVE;  

--5、在目标物化视图上创建索引

CREATE INDEX IDX_T_DV_CT ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME) TABLESPACE &BISON_IDX;

CREATE INDEX IDX_T_DV_UT ON AICS_DEV_INFO (UPDATE_TIME) TABLESPACE &BISON_IDX;

CREATE INDEX I_T_DV_MSISDN ON AICS_DEV_INFO (MSISDN) TABLESPACE &BISON_IDX;

1）使用dbms_mview.refresh 手工刷新

--完全刷新

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');

EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');

--快速刷新

EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');

EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');

2）使用dbms_refresh.refresh 过程来批量刷新MV

如果我们在创建物化视图的过程指定start 和next time的刷新时间，那么Oracle 会自动创建刷新的job，并采用dbms_refresh.refresh 的方式。

使用这种方式刷新之前需要先make refresh group，然后才可以刷新。

Refreshmake 的语法可以参考：

http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057

示例：

假设存在物化视图MV_T1, MV_T2, MV_T3.

创建refresh group的语法如下：

EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')

--刷新整个refresh group 组：

EXEC DBMS_REFRESH.REFRESH('REP_TEST')

--查看物化视图定义

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_OTM_ORDER','RFUSER') from dual;

--查看物化视图上的索引

SELECT * FROM DBA_INDEXES WHERE TABLE_NAME='MV_OTM_ORDER';

--删除源端物化视图日志：

DROP MATERIALIZED VIEW LOG ON DAVE;

--删除目的端物化视图

DROP MATERIALIZED VIEW MV_DAVE;

select owner,mview_name,query,master_link,refresh_mode,refresh_method,last_refresh_date,staleness,compile_state from dba_mviews;

SELECT OWNER,NAME, MASTER_OWNER,MASTER,LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;

--1、查询所有mvlog,找到表名和mvlog表名

SELECT LOG_OWNER,MASTER,LOG_TABLE,,ROWIDS,PRIMARY_KEY,LAST_PURGE_DATE FROM DBA_MVIEW_LOGS;

--2、查询mvlog

SELECT * FROM MLOG$_TT;

select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS;