7. 如果添加的表没有主键需要设置可以代替主键的列或者列的集合:
dbms_repcat.set_columns( sname => ’SHORACL’, oname => ’ CREDIT_CARD ’, column_list => ’ CREDIT_CARD_ID’); end; /
dbms_repcat.generate_replication_support( sname=>’SHORACL’, oname=>’ CREDIT_CARD’, type=>’TABLE’, min_communication=>TRUE); end; / 9. 为快速刷新创建实体化视图日志:
rowid excluding new values;
begin dbms_repcat.resume_master_activity( name=>’sh_rep’); end; / 二.设置实体化视图站点:
connect system/passwd@BJ; create user mvadmin identified by mvadmin; begin dbms_repcat_admin. grant_admin_any_schema(username=> ’mvadmin’); end; / grant comment any table to mvadmin; grant lock any table to mvadmin; grant select any dictionary to mvadmin; 2.注册传播方:
dbms_defer_sys.register_propagator( username => ’mvadmin’); end; / 3.公共数据库连接。需要每个复制需要创建三个数据库连接。公共数据库连接指定数据库的全局名称:
create public database link ORACLSH using ’Oracle.shanghai. com’; Using子句后跟的是全局数据库名或者是连接字符串。
create public database link ORACLSH using ’(description= (address=(protocol=tcp)(host=127.0.0.1)(port=1521)) (connect_data=(service_name=oracl)))’ 4.建立清除延迟事务队列调度作业:
connect mvadmin/mvadmin@BJ; begin dbms_defer_sys.schedule_purge( next_date => sysdate, interval => ’/*1:hr*/ sysdate + 1’, delay_seconds => 0, rollback_segment => ’’); end;
identified by proxy_bjoralce Connect to ... Identified by ...子句指明用什么用户连接远程数据库 6.建立复制调度数据库连接作业:
dbms_defer_sys.schedule_push( destination => ’ora92zjk’,interval => ’/*1:hr*/ sysdate + 1’, next_date => sysdate,stop_on_error => false, delay_seconds => 0,parallelism => 0); end; / 7.授予SHORACL用户(对应SHORACL方案)相应的权限建立实体化视图:
connect system/passwd@BJ; grant alter session to crm; grant create cluster to crm; grant create database link to crm; grant create sequence to crm; grant create session to crm; grant create synonym to crm; grant create table to crm; grant create view to crm; grant create procedure to crm; grant create trigger to crm; grant unlimited tablespace to crm; grant create type to crm; grant create any snapshot to crm; grant alter any snapshot to crm; 8.建立复制方案的数据库连接:
connect SHORACL/SHORACL@BJ; create database link ORACLSH connect to ORACL identified by ORACL; 复制方案的数据库连接和复制管理员的数据库连接要和system用户间里的对应公共数据库连接使用相同的名字,在调度连接时将使用公共数据库连接中指定的数据库全局名或者连接字符串。
connect mvadmin/mvadmin@BJ; create materialized view SHORACL.CREDIT_CARD refresh fast wit h pr imar y key as sele ct * from ORA CL. CREDIT_CARD@ORACLSH; @后面是数据库连接名。如果该表没有主键则使用rowid来刷新。
fast with rowid as select * from ORACL. CREDIT_CARD@ORACLSH; 10.为多个视图建立刷新组:
dbms_refresh.make ( name => ’mvadmin.sh_refresh’,list => ’’, next_date => sysdate,interval => ’sysdate + 1’, implicit_destroy => false,rollback_seg => ’’, push_deferred_rpc => true,refresh_after_errors => false); end; 11.向刷新组中添加复制对象:
dbms_refresh.add (name => ’mvadmin.sh_refresh’, list => ’SHORACL.CREDIT_CARD ’,lax => true); end; / 三.检查复制进程:
purge( delay_seconds=>0); end; b) declare rc binary_integer; begin rc := sys.dbms_defer_sys. push(destination=>’ORACLSH’, stop_on_error=>FALSE, delay_seconds=>0, parallelism=>0); end; c) dbms_refresh.refresh(’"MVADMIN"."SH_REFRESH"’); 如果排除其它系统作业本例中杭州和武汉的数据库复制建立之后将会有7个作业(如果为每个复制分别建立刷新组的话),清除作业始终只有一个。每个复制对应一个调度作业,每个刷新组对应一个刷新作业。
dbms_job.run(job_no); end; /
|