oracle数据库物化视图创建，以及数据中心同步数据步骤

--HCM创建主键mlog文件

CREATE MATERIALIZED VIEW LOG ON TB_STA_EMP_ORG WITH ROWID ,PRIMARY KEY including new values;

--HCM创建ROWIDmlog文件

CREATE MATERIALIZED VIEW LOG ON TB_STA_EMP_ORG WITH ROWID including new values;

--删除物化log
drop MATERIALIZED VIEW LOG ON TB_CNB_PAY_RECORD;

--查询所有mlog文件
select 'SELECT COUNT(*) FROM '||TABLE_NAME||';' from user_tables l where l.table_name like 'MLOG%'

--HCM中创建主键mlog
CREATE MATERIALIZED VIEW tb_inf_826291               
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
START WITH sysdate NEXT TRUNC(SYSDATE,'MI')+1/(24*60)
AS select * from tb_inf_826291@HCMDB;

--HCM中创建ROWIDmlog
CREATE MATERIALIZED VIEW tb_inf_826291               
REFRESH FAST ON DEMAND
WITH ROWID
START WITH sysdate NEXT TRUNC(SYSDATE,'MI')+1/(24*60)
AS select * from tb_inf_826291@HCMDB;

--删除物化视图
DROP MATERIALIZED VIEW TP_SECURITY_USER_ADDITION_INFO;

--更新物化视图刷新时间    1为1分钟
alter MATERIALIZED VIEW tb_inf_826291                    
refresh FAST on demand start with
sysdate next trunc(sysdate,'mi')+1/(24*60);

--获取move   sql语句
select 'ALTER TABLE '|| TABLE_NAME || ' MOVE TABLESPACE MVLOG;' from user_tables l where l.TABLE_NAME like 'MLOG%';