1. connect string需加在DB Server

MTSMC2_PSCVUTF8=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ABC)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = DEF)

      (SERVER = DEDICATED)

    )

  )

 

D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora

 oracle snapshot1.png  

 

  1. 建立DB Link

2.1 建立DB Link

 oracle snapshot2.png  

2.2 設定Related Connection

 oracle snapshot3.png  

2.3測試DB link是否成功

select count(*) from tableA@link

 

  1. 建立snapshot
  2. CREATESNAPSHOT <snapshotname(同時也是存放資料的table名稱)>

    TABLESPACE <Tablespacename>

    STORAGE (INITIAL100K NEXT50K PCTINCREASE0)

    REFRESHCOMPLETE

            STARTWITH to_date(to_char(sysdate+1, 'dd-mm-yy') || ' 06:00:00', 'dd-mm-yy hh24:mi:ss')

            NEXT to_date(to_char(sysdate+1, 'dd-mm-yy') || ' 06:00:00', 'dd-mm-yy hh24:mi:ss')

            AS

            select * from <SourceTable>@<DB Linkname>;

 

P.S刪除Snapshot: dropsnapshot <snapshotname>

 

 

4.更改snapshot refresh時間

4.1 Alter snapshot <snapshot_name> refresh complete

  Start with sysdate+1/2880 next sysdate+1;

 

  {SQL語句的意思:設定oracle自動在30

  (30/24*60*60)後進行第一次完全刷新,

  以後每隔1天完全刷新一次。}

  4.2  Alter snapshot item_snapshot refresh fast

          Start with sysdate+1/1440 next sysdate+1/144;

 

  {SQL語句的意思为:設定oracle自動在1分钟

  (1/24*60)後進行第一次快速刷新,以後每隔10分钟

  (10/24*60)快速刷新一次。}

 

example

1.

CREATESNAPSHOT tableB

TABLESPACE USERS

STORAGE (INITIAL100K NEXT50K PCTINCREASE0)

REFRESHCOMPLETE

        STARTWITHsysdate

        NEXT to_date(to_char(sysdate+1, 'dd-mm-yy') || ' 06:00:00', 'dd-mm-yy hh24:mi:ss')

        AS

        select * from tableA@link;


查詢Snapshot相關資訊:

SELECT * FROM DBA_REFRESH

arrow
arrow
    全站熱搜

    Big Bear 發表在 痞客邦 留言(0) 人氣()