Programs Knowledge

0. 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

0. 建立DB Link

2.1 建立DB Link

2.2 設定Related Connection

2.3測試DB link是否成功

select count(*) from tableA@link

0. 建立snapshot

CREATE SNAPSHOT <snapshot name(同時也是存放資料的table名稱)>

TABLESPACE <Tablespace name>

STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 0)

REFRESH COMPLETE 

        START WITH 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 <Source Table>@<DB Link name>; 

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.

CREATE SNAPSHOT tableB

TABLESPACE USERS

STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 0)

REFRESH COMPLETE

        START WITH sysdate

        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_FRERESH