利用 mviews on prebuilt table 进行增量刷新数据

最新推荐文章于 2025-05-12 13:59:36 发布

jnrjian

最新推荐文章于 2025-05-12 13:59:36 发布

阅读量237

点赞数

文章标签：数据库 sql oracle

本文链接：https://blog.csdn.net/jnrjian/article/details/127609038

版权

本文围绕Oracle数据库展开，介绍了表迁移及物化视图操作。包括在源库创建表和物化视图日志、授权、在目标库创建相同表和预建物化视图，还涉及完全刷新和增量刷新操作，最后进行停机切换、清除物化视图和日志等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.在源库上创建表和mview log

create table andy_01 as select * from dba_objects ;

select count(*) from andy_01;

--为基表建立主键，防止ORA-12014 does not contain a primary key constraint
delete from andy_01 where object_id is null;
alter table andy_01 add constraint pk_andy_01 primary key(object_id);
create materialized view log on andy_01;

--源库查询生成 materialized view log 对象名

select * from user_objects where object_name like '%MLOG$%';
OBJECT_NAME
-----------------------------------------------------------------------------
I_MLOG$_ANDY_01 --index
MLOG$_ANDY_01 -> materialized view log 对象名

select *from MLOG$_ANDY_01

2. 授权

-- 授权目标库用户有查询源库 materialized view log 的权限
grant select on scott.MLOG$_ANDY_01 to andy;

grant select on scott.andy_01 to andy;

3.在目标数据库上创建与该表一样的表，并在该表上创建prebuilt mv

create table andy_02 as select * from andy_01 where 1=2; --异库加上@dblink_name

select count(*) from andy_02;
COUNT(*)
----------
0
-- sys用户为目标用户授权
SQL> GRANT CREATE MATERIALIZED VIEW TO andy;

Grant succeeded.
-- 目标库创建 materialized view

SQL> create materialized view andy_02 on prebuilt table refresh fast as select * from andy_01;
Materialized view created.

select *From dba_objects b where b.OBJECT_NAME like '%ANDY_02%'

select *From andy_02

4.做完全刷新和增量刷新

begin dbms_mview.refresh('andy_02','Complete');
end;

--此时模拟在做完全刷新过程中，源库的表又发生了变化
SQL> insert into andy_01(object_id,owner) values(666666,'test');

1 row created.
SQL> commit;

Commit complete.
--再做增量刷新
select count(*) from andy_02;

COUNT(*)
----------
88765
begin dbms_mview.refresh('andy_02','Complete');
end;

PL/SQL procedure successfully completed.

SQL> select count(1) from andy_01;

5.停机切换,做最后一次刷新，然后删除源库的mview log和目标库的mview

SQL> exec dbms_mview.refresh('andy_01');
PL/SQL procedure successfully completed.

6. 迁移完毕，清除 materialized view 与 materialized view log

-- 清除目标库 materialized view
SQL> drop materialized view andy_02;
Materialized view dropped.
SQL> select count(*) from andy_02;
COUNT(*)
----------
88766
这里删除的mview(andy_01)是prebuilt mv，所以删除该mview,并不删除相应的表。---
-- 清除源库 materialized view log
SQL> drop materialized view log on ANDY_01;

Materialized view log dropped.

SQL> select * from user_objects where object_name like '%ANDY%';

no rows selected