--======问题的提出========--
/**
XXX项目组，要求我们想个办法，
解决XXX数据库（英文字符集）抽取XXX（中文字符集）数据库的问题，

*/

-- Multitier Materialized Views

CREATE MATERIALIZED VIEW hr.employees REFRESH FAST FOR UPDATE AS
SELECT * FROM hr.employees@orc1.world e
WHERE EXISTS
(SELECT * FROM hr.departments@orc1.world d
WHERE e.department_id = d.department_id
AND EXISTS
(SELECT * FROM hr.locations@orc1.world l
WHERE l.country_id = 'UK'
AND d.location_id = l.location_id));
/**
-- ============= 实验流程==============--
1 从中文-车购税模拟库上，导出一个表做测试，GD_CLDA ，保留100条记录
2 将这个表导入到笔记本的中文-testdb数据库中,做为Master Table
3 在testdb数据库中建level1 MV,只做PK型，不加函数
4 在orcl英文库中建level2 MV

重复3、4步，加上函数。

在做3步时，遇到LARGE POOL分配错误的问题。

*/

--==== 在testdb数据库上的操作 中文字符集 ===--
-- 导入测试用主表
SQL> @D:zl_12366CheGouShuiDZSYDAgd_clda.sql

-- 在主表上建快照日志
SQL> create materialized view log on gd_clda;

Materialized view log created

-- 在主表上建一个全部字段无函数的MV
SQL> create materialized view v_gd_clda
2 refresh fast
3 as select * from gd_clda;

Materialized view created

-- 在主表上建一个带函数的MV
SQL> create materialized view v_gd_clda2
2 refresh fast
3 as select da_bs,utl_raw.cast_to_raw(nsr_mc) as nsr_mc from gd_clda;

Materialized view created

-- 在MV上增加MV LOG
SQL> create materialized view log on v_gd_clda;

Materialized view log created

SQL> create materialized view log on v_gd_clda2;

Materialized view log created

-- 到英文库上建MV，然后回来插入一条测试记录
-- 增量刷新带函数的MV
SQL> exec dbms_mview.refresh('v_gd_clda2','f');

PL/SQL procedure successfully completed
-- 确定刷新成功
SQL> select count(1) from v_gd_clda2;

COUNT(1)
----------
101

SQL>

--========在ORCL数据库上的操作 英文字符集=========--
-- 建一个数据库链路
SQL> create database link testdb
2 connect to scott identified by tiger using 'testdb';

Database link created
-- 验证链路
SQL> select count(1) from user_tables@testdb;

COUNT(1)
----------
28

-- 创建增量更新MV
SQL> create materialized view gd_clda1
2 refresh fast
3 as select * from v_gd_clda@testdb;

Materialized view created

SQL> create materialized view gd_clda2
2 refresh fast
3 as select da_bs,utl_raw.cast_to_varchar2(nsr_mc) as nsr_mc from v_gd_clda2@testdb;

Materialized view created

-- 验证MV的创建
SQL> select nsr_mc from gd_clda2;

-- 在中文库有新记录时，增量刷新本地快照
SQL> exec dbms_mview.refresh('gd_clda2','f');

PL/SQL procedure successfully completed

-- 验证记录数的增长
SQL> select count(1) from gd_clda2;

COUNT(1)
-------------------
101

-- 查询刚才插入的记录
SQL> select nsr_mc from gd_clda2 where nsr_mc='王旺';

NSR_MC
--------------------------------------------------------------------------------
王旺

--===============END ==================--