9.11 Database Link

Oracle Database Link (DB Link) 簡單來說，就是在兩個不同的 DB 之間建立起一個橋樑，提供了跨資料庫查詢的一個途徑，例如有兩個資料庫 DB1 與 DB2 ，由 DB1 建立一個 DB Link 到 DB2 ，那麼就可以由 DB1 發起一條查詢的指令來查詢 DB2 的 Table :

Database Link 的原理是透過 Tnsnames 來連線到另一個資料庫，換句話說，我們建立 DB Link 其實就是在告訴系統如何連線到另一個資料庫，由系統自動幫我們連線到另一個資料庫並且把資料撈取回來。 DB Link 可以分為 Public Database Link 與 Private Database Link 兩種， Public Database Link 表示此資料庫的所有使用者都可以使用；而 Private Database Link 只有建立此 DB Link 的使用者可以使用。使用 create database link 的語法來建立:

SQL> create public database link ora12 connect to hr identified by hr using 'ORA12';       (建立 public DB Link) SQL> create database link ora12 connect to hr identified by hr using 'ORA12';       (建立 private DB Link)

其中:

• connect to : 表示連線到另一個資料庫的使用者名稱。

• identified by : 表示連線使用者的密碼。

• using : 表示連線到另一個資料庫的 Tnsnames ，需要先在 $ORACLE_HOME/network/admin/tnsnames.ora 裡面設定。

這邊要注意的是，只有在後面的 using 才需要使用單引號，前面輸入帳號密碼不需要用單引號。如果要建立的是 Public Database Link 只需要加上 public 關鍵字就可以了。建立好 DB Link 之後，我們就可以使用 @ 語法來查詢另一個資料庫的資料，例如查詢 ORA12 資料庫的 employees :

SQL> select * from employees@ORA12;

要得知目前資料庫有多少個 Database Link ，使用 dba_db_links 來查詢:

SQL> select * from dba_db_links;

Database Link 與資料庫的 Global Name 有著一定的關係， Global Name 預設為 db_name.db_domain ，在資料庫建立時如果有設定 db_domain 參數，那麼 Global Name 就會自動設定為 db_name.db_domain ，如果在創建時沒有設定 db_domain ，那麼即便後來設定了 db_domain ， Global Name 也不會自動變更，例如 ORA11 這個資料庫在建立時設定 db_domain 為 oracle.com ，那麼它的 Global Name 就為 ORA11.oracle.com 。

當參數 global_names 設定為 TRUE 的時候，此時所建立 DB Link 的名稱就會帶上 db_domain ，並且此 DB Link 的命名必須與所連線資料庫的 Global Name 名稱相同才能夠成功的連線，例如目前資料庫的 Global Name 為 ORA11.ORACLE.COM ，那麼所建立的 DB Link 也會自動帶上 ORACLE.COM :

雖然成功建立了，但很有可能還是會連線失敗:

這個訊息說明 DB Link 的名稱為 ORA12.ORACLE.COM 但是對方資料庫的 Global Name 為 ORA12 ，這兩者是不相同的，所以無法成功連線過去，此時必須要把 ORA12 這個資料庫的 Global Name 改為 ORA12.ORACLE.COM 才可以成功的連線:

這邊順帶提一下，更改 Global Name 的方式有兩種，一種是使用 alter database rename global_name ，一種是直接 update sys.PROPS$ :

SQL> alter database rename global_name to ORA11.ORACLE.COM;       (更改 global name 為 ORA11.ORACLE.COM) SQL> update sys.PROPS$ set value$='ORA11' where name='GLOBAL_DB_NAME'; SQL> commit;       (更改 global name 為 ORA11)

總結來說，當參數 global_names 為 TRUE 的時候， DB Link 的名稱必須使用對方資料庫的 Global Name ，當 Global Name 帶有 db_domain 時， DB Link 的名稱也會自動帶上 db_domain 。建議使用 DB Link 這項功能時， global_names 使用預設的 FALSE ，這樣名稱的設定會比較直觀，我們想要替 DB Link 取甚麼名稱就取甚麼，也不用考慮到對方資料庫的 Global Name ，用起來也不會有 db_domain 的問題。最後要注意的是，當 DB Link 建立之後，就不可以隨意地更改 global_names 參數或是 Global Name ，否則會造成系統無法辨識已經存在的 DB Link 名稱，會出現如下情況，明明可以查詢的到，但是系統卻無法辨識:

若要驗證 DB Link 的連線是否可行，最簡單的方式就是執行一段 select 能否成功，問題就在於 Private Database Link 必須要以 DB Link 的擁有者登入才可以進行測試連線，其它使用者是無法驗證 Private Database Link 的連線的:

 

此時可以藉由建立下列 Procedure 來測試 Private Database Link ，把這個 Procedure 建立在 Private Database Link 的擁有者底下，以這個例子來說就是建在 HR 底下來驗證 priv_ora12 這個 Private Database Link :

create or replace procedure hr.dblink_test(link_name varchar2) is type lnkcurtyp is ref cursor; cur_link_name lnkcurtyp; glname varchar2(30); begin  open cur_link_name for 'select * from global_name@'||link_name;  loop   fetch cur_link_name into glname;   exit when cur_link_name%notfound;   dbms_output.put_line('GL_NAME : '||glname);  end loop;  close cur_link_name; end; /

透過這個 Procedure ，即便不是使用 DB Link 擁有者也可以驗證 Private Database Link 是否可以連線:

最後要提的是，從 Oracle 11.2.0.4 開始，增加了安全性，已經無法查得 DB Link 連線使用者的密碼了:

 

而 DB Link 的建立又要求輸入連線使用者的名稱與密碼，那麼要怎麼移轉 DB Link 的設定 ? 這個時候就必須使用 Data Pump 將 DB Link 的設定 export 再 import 到目標端，使用這種方式就可以避免掉帳號密碼的問題，執行 expdp 時使用 full=y 與 include=db_link 就可以單獨匯出 Database Link :

雖然 Database Link 可以跨越資料庫來查詢資料，但畢竟它還是透過網路連線把資料撈回來，建議不要使用在太複雜的 SQL 查詢上，例如使用 DB Link 進行過多的 join 或是子查詢，這種情況當效能出現問題時會不容易進行優化。

 

9.10 Sequence 與 Synonym

Sequence 就是一組序列號，每次取號後自動增加，一般用來作為需要照數列排序的地方，或者是作為欄位唯一值的設定，例如訂單編號，當接收到一筆訂單之後會賦予這筆訂單一個編號，格式可能是 “PO0001” ，在 “PO” 後面賦予一個數值，此時使用 Sequence 取號就可以代表訂單下單的順序，以及確保每一筆訂單編號都是唯一值。 Sequence 是一個獨立的 Object ，使用 create sequence 來建立，例如建立一個名為 po_seq 的 Sequence :

SQL> create sequence po_seq minvalue 0 maxvalue 1000 increment by 1 start with 1 cache 20 order  cycle;

其中:

• minvalue : 設定此 sequence 的最小值，預設為 nominvalue ，從 1 開始。

• maxvalue : 設定此 sequence 的最大值，預設為 nomaxvalue ，最大可達 10 的 28 次方減 1 。

• increment by : 表示此 sequence 每個序列號增加多少，設定為 1 表示每個序列號增加 1 ，即 1 、 2 、 3 … 如此下去。

• start with : 設定此 sequence 從第幾號開始增加。

• cache : 設定 sequence 事先要把多少個序列號放入 cache 中，設定為 20 表示先將 20 個號碼放入 cache ，從 cache 取號有助於提升效能。對於 RAC 環境來說， cache 的設定是必須的，因為 RAC 透過兩個 Instance 同時取號的話很容易會發生 enq: SQ – contention 的等待，透過 cache 的設定可以避免此等待事件的發生，建議在 RAC 環境下 cache 設定為 100 以上。

• order : 此設定是確保 sequence 序列號是按照數列增加的，在有設定 cache 的 RAC 環境下，照時間的先後所取出來的序列號可能是跳號的，例如 cache 設置為 20 ，那麼第一個 Instance 會先 cache 1 ~ 20 的號碼；而第二個 Instance 會 cache 21 ~ 40 的號碼，如果從 Instance 1 與 Instance 2 輪流來取號的話，所產生的順序可能為 1 、 2 、 21 、 3 、 22 … 產生跳號的情況。使用 order 的設定是確保在這種有 cache 的情況下也不會跳號，即便從不同的 Instance 取號，取出的順序仍然會是照順序的 1 、 2 、 3 … 。雖然 order 的設定可以確保不會跳號，但效能上會比 noorder 還來的差，有些系統的序列號只是要確保此欄位的資料不會重複，並沒有一定要照順序的要求，那麼就可以使用 noorder 來提升效能。這個項目預設是 noorder 。

• cycle : 設定當 sequence 達到 maxvalue 之後，是否要回到從第一個號碼開始，預設為 nocycle 。

Sequence 取號的方式是使用 nextval 函式，例如:

SQL> select po_seq.nextval from dual;         (直接取號) SQL> insert into products values (po_seq.nextval, sysdate);       (insert 的時候帶入 sequence 序列號)

這邊要注意的是，只要進行過 nextval 取號， Sequence 就無法回到上一號。

Sequence 的資訊可以透過 dba_sequences 或是 user_sequences 來查詢。如果有 Sequence 重建的需求時，可以利用 dba_sequences 抓出每個 Sequence 目前的序列號以及相關的屬性，進而組合出 Sequence 建立的語法，例如抓出 HR 與 SCOTT 兩個使用者的所有 Sequence 設定:

SQL> select 'drop sequence '||sequence_owner||'.'||sequence_name||';'||chr(10)||' create sequence '||sequence_owner||'.'||sequence_name||' minvalue '||min_value||' maxvalue '||max_value||' increment  by '||increment_by||' start with '||to_char(last_number)||case when cache_size > 0 then ' cache '||cache_size else ' nocache ' end||case order_flag when 'Y' then 'order ' else ' noorder ' end||case cycle_flag when 'Y' then ' cycle ' else ' nocycle ' end||';' as "seq_create"   from dba_sequences where sequence_owner in ('HR','SCOTT') order by sequence_owner;

Synonym 代表的是同義字，當使用者要存取其他使用者的物件時，必須使用 schema.object_name ，若是建立了同義字，那麼就可以簡化語法，物件前面不需要在加上 “schema.” 。 Synonym 可以分為 Public Synonym 與 Private Synonym ， Public Synonym 表示為公用的同義字，必須具有 DBA 權限才可以建立，所有使用者都可以使用 Public Synonym ， 例如建立一個 Public Synonym emp 來代表 hr.employees 這個 Table :

SQL> create public synonym emp for hr.employees;

Synonym 的定義也可以使用 db link ，例如建立 Public Synonym jobs 代表 HR 資料庫的 open_job 這個 Table :

SQL> create public synonym jobs for open_job@hr;

Private Synonym 表示同義字只有當前的使用者可以使用，也就是使用者將自己的某一個物件設定為另外一個別名而已，例如 hr 這個使用者建立一個 Private Synonym dept 來代表 departments 這個 Table:

SQL> create synonym dept for departments;

查詢 Synonym 相關資訊可以由 dba_synonyms 或 user_synonyms 來得知。

9.9 Materialized View

Materialized View 與 View 最大的不同是， View 本身只是存放一段 SQL 語法，針對 View 做查詢時，實際上是執行 View 本身的 SQL 語法；而 Materialized View 不僅僅是有一段 SQL 語法，更重要的是它直接把這段 SQL 語法所產生的結果直接存放起來，由於 Materialized View 是有存放資料的，因此它會占掉 Tablespace 的空間，就結構來說， Materialized View 就有如 Table 一樣，只不過它的資料來源是由一串 SQL 語句所產生，例如一個 Materialized View 的資料來源是 emp 與 departments 兩個 Table:

透過 SQL 語法將查詢 emp 、 departments 兩個 Table 的結果存放在 Materialized View 當中，由於 emp 與 departments 的資料可能會隨著時間有所異動，因此 Materialized View 會有一個 Refresh 的動作來更新這些異動之後的結果。

Materialized View 早期在 Oracle 8i 之前稱作 Snapshot ，其實這個名稱應該是比較合適，因為它是把一串的查詢結果儲存下來，然後定期的去更新這個結果，這個行為就有如資料的 Snapshot 一般，到了 Oracle 9i 才改稱為 Materialized View 。 Materialized View 資料更新 (Refresh) 的方式可分為增量同步 (Fast) 與全量同步 (Complete) ，增量同步是只有更新差異性的資料，這個方式需要建立 Materialized View Log 來記錄差異性的資料，而全量同步則不需要 Materialized View Log 。 Materialized View 的同步時間可以分為 On Demand 與 On Commit 兩種， On Demand 表示 Materialized View 只有在需要更新的時候才進行更新，可以是手動執行 DBMS_MVIEW.REFRESH 來進行更新，或是設定排程來定期更新；而 On Commit 表示在有資料異動且已經 Commit 之後立即進行更新，這種方式很明顯的是一種增量差異性的更新，所以 On Commit 條件下必須是要建立 Materialized View Log 。

建立 Materialized View 使用 create materialized view 語法，例如建立一個 On Demand 的 Materialized View:

SQL> create materialized view mv_emp as  select employee_id,salary,department_name from emp e,departments d  where e.department_id=d.department_id;

建立 Materialized View 不加任何選項，預設就是 On Demand 模式，不會自動 Refresh ，必須執行 DBMS_MVIEW.REFRESH 來更新資料:

SQL> BEGIN    DBMS_MVIEW.REFRESH( 'MV_EMP',method => '?', atomic_refresh => false); END; /

其中 method 為執行 Refresh 的方式，可設定為:

• f : 表示進行 fast refresh (增量同步) ，必須要有 Materialized View Log 。

• c : 表示進行 complete refresh (全量同步) 。

• ? : 表示進行 force refresh ，由系統自行判斷要使用 fast 或是 complete refresh ，一般來說會以 fast refresh 為優先，無法進行 fast refresh 時 (例如沒有 Materialized View Log) ，就會執行 complete refresh 。

atomic_refresh 是用來設定 complete refresh 時的更新方式，預設為 TRUE ，表示進行 complete refresh 時會先將目前所有的資料 delete 然後再 insert 新的資料；設定為 FALSE 表示會使用 truncate 清除現有的資料然後再 insert 新的資料，在資料量很大的時候建議將此選項設定為 FALSE 來避免因為 delete 而產生過多的 archive log 。

另外也可以建立定時做 Refresh 的 Materialized View ，例如建立一天 Refresh 一次的 Materialize View :

SQL> create materialized view mv_emp  refresh force start with sysdate next (sysdate + 1)  with primary key  as select employee_id,salary,department_name from emp e,departments d  where e.department_id=d.department_id;

其中 refresh 用來設定 refresh 的方式，預設為 force ，可以設定為 complete 或是 fast ； start with 是用來設定 refresh 的時間，設定之後會自動於排程新增一個 job 來執行 refresh 的動作 ； with primary key 表示增量同步時以 primary key 為基準來更新差異的資料， primary key 為預設選項，如果沒有 primary key 或無法使用 primary key 的情況之下，可以使用 with rowid ，以 rowid 的方式來進行差異資料的更新。

如果要建立 On Commit 或是能夠 fast refresh 的 Materialized View ，必須要先為 Based Table 建立 Materialized View Log ，例如 emp 沒有建立 primary key 所以使用 with rowid 選項， departments 有 primary key 則使用預設的 with primary key :

SQL> create materialized view log on emp with rowid; SQL> create materialized view log on departments with primary key;

建立好 Materialized View Log 之後就可以建立一個 On Commit Refresh 的 Materialized View 了 :

SQL> create materialized view mv_emp  refresh force on commit with primary key  as select employee_id,salary,department_name from emp e,departments d  where e.department_id=d.department_id;

如果有多個 Materialized View 需要排程定期的來 Refresh ，可以使用 DBMS_REFRESH.MAKE 建立一個 Refresh Group 來定期的 Refresh 這個 Group 底下的所有 Materialized View ，例如建立一個一天 Refresh 一次的 Refresh Group ，其中包含了兩個 Materialized View 在這個 Group 裡面:

SQL> begin        dbms_refresh.make(name=>'mv_group1',list=>'mv_emp1,mv_emp2',                              next_date=>sysdate,interval=>'sysdate + 1');       end; /

其中 name 表示設定 Refresh Group 的名稱; list 表示要納入此 Group 的 Materialized View ; next_date 表示開始執行 Refresh Group 的時間; interval 表示間隔多久執行一次 Refresh ，這邊 sysdate + 1 表示一天執行一次，如果是一個小時執行一次，則可以設定為 sysdate + 1/24 。

Refresh Group 除了定時自動執行外，也可以使用 DBMS_REFRESH.REFRESH 手動進行 Refresh ，執行 DBMS_REFRESH.REFRESH 會 Refresh 這個 Group 底下所有的 Materialized View :

SQL> begin        dbms_refresh.refresh('mv_group1');      end; /

如果要將一個 Materialized View 添加到這個 Group ，則使用 DBMS_REFRESH.ADD ，例如增加 mv_emp3 到 mv_group1 :

SQL> begin        dbms_refresh.add(name=>'mv_group1',list=>'mv_emp3');      end; /

如果要將 Materialized View 移出 Group ，則使用 DBMS_REFRESH.SUBTRACT ，例如將 mv_emp1 移出 mv_group1 :

SQL> begin        dbms_refresh.subtract(name=>'mv_group1',list=>'mv_emp1');      end; /

如果要刪除 Refresh Group ，則使用 DBMS_REFRESH.DESTORY ，刪除 Refresh Group 同時會一起將這個 Group 底下所有的 Materialized View 移出這個 Refresh Group:

SQL> begin        dbms_refresh.destroy('mv_group1');      end; /

要得知 Refresh Group 的相關資訊，可以從 dba_refresh 、 dba_refresh_children 或是 user_refresh 、 user_refresh_children 來查詢:

如果要得知 Materialized View 的相關資訊，可以從 dba_mviews 、 user_mviews ，或者是查詢 dba_objects 的 object_type='MATERIALIZED VIEW' :

Materialized View 是將一段查詢的結果存放起來，預設這些數據是不能異動的，如果有特殊需求，可以創建 Writeable Materialized View ，但限制上必須要可以執行 fast refresh 並且不能使用複雜的語法如 join 、 subquery 、 union 、 connect by 、 order by 、 group by…等 ，在建立時加上 for update 就可以建立 Writeable Materialized View :

SQL> create materialized view log on departments with primary key; SQL> create materialized view w_mv_dep for update as select department_id,department_name from departments;

由於 Writeable Materialized View 必須要可以執行 fast refresh ，所以一定要先創建 Materialized View Log ，這邊要注意的是，如果 Materialized View Log 使用的是 with rowid ，那麼 Writeable Materialized View 也要必須使用 refresh fast with rowid 才可以建立，否則就會出現 ORA-12013: updatable materialized views must be simple enough to do fast refresh 的錯誤。

Materialized View 實務上最常運用的就是做資料的同步，例如建立一個 Materialized View 透過 DB Link 將員工資料由 HR 資料庫定期同步過來:

SQL> create materialized view mv_hr_emp  refresh force start with sysdate next (sysdate + 1)  with primary key  as select * from employees@HR;

另一個運用就是可以透過內部 Query Rewrite 機制來提升 SQL 的效能，由於 Materialized View 是事先將結果儲存起來，對於一些複雜的 SQL ，其執行計畫可以藉由存取 Materialized View 來快速的得到查詢的結果，例如一個 SQL 用來查詢每個部門的薪資總額 ( sum(salary) ) ，其執行計畫是對 emp 做 full table scan:

如果我們事先將 sum(salary) 這個部分建立 Materialized View ，那麼同樣查詢的執行計畫就會導向使用 Materialized View 來存取，這時在建立 Materialized View 的時候必須加上 enable query rewrite 才會被執行計畫所套用:

由於 Materialized View 已經將結果存放起來，所以走這個經由 Materialized View 的執行計畫，效能上會比原本直接存取 emp 再進行 sum(salary) 來的好，不過這個方法需要注意的是， Materialized View 的資料可能與原始 Table 不同步，這樣有可能造成查詢出來的資料是錯誤有差異的，如果要使用 Query Rewrite 的功能，最好是使用 On Commit 的 Materialized View ，避免因為資料的差異導致查詢結果有誤差。