如何通過DBLINK取remote DB的DDL

space6212發表於2019-07-14
REM

今天在寫一個資料庫結構同步小工具，需要用到dbms_metadata.get_ddl這個包取物件的DDL。這個包取本地物件的DDL是沒有問題的，但在這個工具中，需要通過DBLINK方式取遠端資料庫的DDL，經過一番資料查詢，找到了解決方法。

[@more@]

首先嚐試直接在dual用dblink，結果出錯：


SQL> SELECT (DBMS_METADATA.GET_DDL('TABLE', 'NEW_A', 'ADMIN')) FROM DUAL@DB230;

SELECT (DBMS_METADATA.GET_DDL('TABLE', 'NEW_A', 'ADMIN')) FROM DUAL@DB230

ORA-31603: object "NEW_A" of type TABLE not found in schema "ADMIN"
ORA-06512: at "SYS.DBMS_METADATA", line 1548
ORA-06512: at "SYS.DBMS_METADATA", line 1585
ORA-06512: at "SYS.DBMS_METADATA", line 1902
ORA-06512: at "SYS.DBMS_METADATA", line 2793
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

ADMIN.NEW_A在DB230是存在的，從這個錯誤可以看出，直接在dual表加dblink是沒有意義的，dbms_metadata還是會從本地資料庫檢視錶是否存在。

接著修改一下SQL，在dbms_metadata也加上dblink，看看結果如何：

SQL> SELECT DBMS_METADATA.GET_DDL@DB230('TABLE', 'NEW_A', 'ADMIN') FROM DUAL@DB230;

SELECT DBMS_METADATA.GET_DDL@DB230('TABLE', 'NEW_A', 'ADMIN') FROM DUAL@DB230

ORA-22992: cannot use LOB locators selected from remote tables

現在錯誤變了，說明在dbms_metadata上加dblink奏效了。新的錯誤很明顯，在SQL方式下，不能直接通過dblink傳送lob，所以需要想辦法把lob轉換為其他型別再傳送：

SQL> SELECT DBMS_LOB.SUBSTR@DB230(DBMS_METADATA.GET_DDL@DB230('TABLE', 'NEW_A', 'ADMIN')) FROM DUAL@DB230;

DBMS_LOB.SUBSTR@DB230(DBMS_MET
--------------------------------------------------------------------------------

CREATE TABLE "ADMIN"."NEW_A"
( "TABLE_NAME" VARCHAR2(32),

"D" DATE,

"BYTES" NUMBER

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADMIN_TS"

可以看到，此時遠端資料庫的DDL已經被成功取過來。注意dbms_lob也要加上dblink，表名在遠端資料庫執行這個函式。

最後簡單總結一下：

1.函式和表都可以加dblink，函式加上dblink表示在遠端資料庫執行該函式，表加上dblink表示從遠端資料庫讀取表。

2. 從遠端資料庫取DDL，所有函式及表(dbms_metadata/dbms_lob/dual)都要加上dblink，否則會失敗

3. 此種方法最多返回32767個位元組，如果物件的DDL長度大於32767，則只能部分返回。在處理裡需特別處理(如分段取DDL)。

4. 如果是在PL/SQL中取遠端資料庫物件的DDL，則可以不用dbms_lob，通過變數賦值的方式不需要dbms_lob截斷，如：

BEGIN
SELECT DBMS_METADATA.GET_DDL@DB230('TABLE','NEW_A','ADMIN') into V FROM DUAL@DB230;
END;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-1035144/，如需轉載，請註明出處，否則將追究法律責任。

相關文章