【IT168 技术文档】前几天都是用Package来完成streams的配置，但在管理上用OEM会感觉更为直观，当然对所有相关的Package很了解的话，也就都差不多了。在9i的OEM中看上去对streams的功能还不是很强。只是简单的一些应用。下面有时间就要搞10gR2，想跳过10gR1的。下面帖一个在OEM建立单表复制的过程脚本。

    源数据库: GATES
    目标数据库: CLONE

    1.设置操作
    - Turn on supplemental logging and switch log file at source database.
    - Create and set default tablespace for LogMiner at source database.
    - 在源数据库中捕获以下各表, 然后传播并将更改应用于目标数据库。
    "SCOTT"."BONUS"

    2.导出/导入操作
    - 导出从源数据库选择的所有对象。
    - 将它们导入到目标数据库。

    3.启动操作
    - 首先在目标数据库中启动应用进程。
    - 在源数据库中启动捕获进程。

    -- Setup Operations:

    REM
    REM The following sections set up streams at the destination database
    REM
    ACCEPT dest_strmadmin_passwd PROMPT '请输入目标数据库
    CLONE 中的用户 STRMADMIN 的口令 : ' HIDE
    connect STRMADMIN/&dest_strmadmin_passwd@CLONE

    REM
    REM Add apply rules for tables at the destination database
    REM
    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => '"SCOTT"."BONUS"',
    streams_type => 'APPLY',
    streams_name => 'STRMADMIN_GATES_VONGATES_',
    queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
    include_dml => true,
    include_ddl => true,
    source_database => 'GATES.VONGATES.COM');
    END;
    /

    REM
    REM The following sections set up streams at the source database
    REM
    ACCEPT source_strmadmin_passwd PROMPT '请输入源数据库
    GATES 中的用户 STRMADMIN 的口令 : ' HIDE
    connect SYS/&source_dba_passwd@GATES as SYSDBA

    REM
    REM Turn on supplemental logging
    REM
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX)
    COLUMNS;

    REM
    REM Switch log file
    REM
    ALTER SYSTEM SWITCH LOGFILE;

    REM
    REM Create logminer tablespace
    REM
    CREATE TABLESPACE LOGMNRTS DATAFILE 'logmnrts_GATES.dbf'
    SIZE 25M REUSE
    AUTOEXTEND ON MAXSIZE UNLIMITED;

    REM
    REM Set logminer tablespace
    REM
    BEGIN
    DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
    END;
    /
    connect STRMADMIN/&source_strmadmin_passwd@GATES

    REM
    REM Add capture rules for tables at the source database
    REM
    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name => '"SCOTT"."BONUS"',
    streams_type => 'CAPTURE',
    streams_name => 'STRMADMIN_CAPTURE',
    queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
    include_dml => true,
    include_ddl => true,
    source_database => 'GATES.VONGATES.COM');
    END;
    /

    REM
    REM Add propagation rules for tables at the source database
    REM
    BEGIN
    DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name => '"SCOTT"."BONUS"',
    streams_name => 'STRMADMIN_PROPAGATE',
    source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"',
    destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@CLONE.VONGATES.COM',
    include_dml => true,
    include_ddl => true,
    source_database => 'GATES.VONGATES.COM');
    END;
    /
    -- Export/Import Operations:
    exp USERID="STRMADMIN"@GATES TABLES="SCOTT"."BONUS"
    FILE=tables.dmp GRANTS=Y ROWS=Y LOG=exportTables.log
    OBJECT_CONSISTENT=Y INDEXES=Y

    imp USERID="STRMADMIN"@CLONE FULL=Y CONSTRAINTS=Y FILE=tables.dmp
    IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log
    STREAMS_CONFIGURATION=Y STREAMS_INSTANTIATION=Y

    -- Startup Operations:
    ACCEPT dest_strmadmin_passwd PROMPT '请输入目标数据库
   CLONE 中的用户 STRMADMIN 的口令 : ' HIDE
    connect STRMADMIN/&dest_strmadmin_passwd@CLONE

    REM
    REM Start apply process at the destination database
    REM
    DECLARE
    v_started number;
    BEGIN
    SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
    FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_GATES_VONGATES_';

    if (v_started = 0) then
    DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_GATES_VONGATES_');
    end if;
    END;
    /

    ACCEPT source_strmadmin_passwd PROMPT '请输入源数据库
    GATES 中的用户 STRMADMIN 的口令 : ' HIDE
    connect STRMADMIN/&source_strmadmin_passwd@GATES

    DECLARE
    v_started number;
    BEGIN
    SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
    FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';
    if (v_started = 0) then
    DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'CAPTURE');
    end if;
    END;
    /