oracle|對象|資料|資料庫 從對象（例如資料庫表、索引、約束、觸發器等）中提取DDL命令的普通方法涉及到的操作包括從這些對象中提取中繼資料（metadata），並把這些資料存放區在記憶體中。儘管目前有很多指令碼可以實現這樣的功能，但是它們通常都是不完整的或者過時的。幸運的是，Oracle 9.2提供了一個實現這樣的功能的API：DBMS_METADATA程式包。
在很多情況下，資料庫中資料的維護操作要求我們提取多種對象（例如資料庫表、索引、約束、觸發器等）的DDL（Data Definition Language，資料定義語言 (Data Definition Language)）。
最近我承擔了一個任務，我需要編寫一組資料庫程式包來執行高效能的大量的資料刪除（DELETE）操作。這樣的操作要求我擁有提取和儲存資料庫物件DDL的相關技術。
提取和儲存資料庫物件的DDL的方法如下：
· 建立與源表結構相同的資料表，但是它不帶主鍵、備用鍵和外部鍵約束。
· 例如，使用MyTable_X，其中MyTable是要被刪除的目標資料表。
· 把需要儲存的資料插入建立立的資料表（MyTable_X）中。
· 使用NOLOGGING PARALLEL選項在新資料表上建立索引。
· 在新資料表上建立約束。
· MyTable和MyTable_X資料表進行交換。把主表改名為MyTable_T，把MyTable_X改名為MyTable。
· 驗證結果並刪除MyTable_T表。
很明顯，為了編寫實現上面目標的代碼，你必須提取資料庫物件的中繼資料（定義和被選中的屬性），並把它儲存在記憶體中，這樣在執行上面的操作的時候才能夠使用它。
在網上存在大量的指令碼，它們可以從多種Oracle資料字典（user_tables、user_indexes、user_ind_columns、user_constraints、user_cons_columns等）中提取資料庫物件的中繼資料，接著為特定的物件建構DDL命令。這些指令碼的一個問題是，它們通常是SQL*Plus指令碼，它會產生用戶端文字檔，而這個檔案不能被伺服器端代碼訪問。它們的主要問題有：
· 不完整：不能提取所有的選項，並組合進DDL語句中。
· 過時了：這些指令碼通常不支援Oracle最新的資料庫特性--分區（partitioning）、基於函數的索引、自動段空間管理（ASSM）等。這些指令碼可能崩潰或建置錯誤的DDL語句。
問題總結：儘管有大量的從Oracle資料字典中提取資料庫物件中繼資料的指令碼，但是它們中的大多數要麼不完整，要麼到期了。
解決方案：使用DBMS_METADATA程式包，學習如何用最佳的、沒有錯誤的和易於維護的方式執行上面的事務。
使用Oracle的本地API：DBMS_METADATA程式包
Oracle資料庫採用補充PL/SQL程式包的形式提供了豐富的預先封裝好的API。Oracle 9.2版本中引入的DBMS_METADATA程式包可能正好適合你的需求。它包含了用於檢索資料庫物件定義的API。
我們將使用的API主要是DBMS_METADATA.GET_DDL函數。這個函數返回的對象定義SQL字串是CLOB。它擁有下面一些輸入參數：
· object_type VARCHAR2
· name VARCHAR2
· schema VARCHAR2 DEFAULT NULL
· version VARCHAR2 DEFAULT ’COMPATIBLE’
· model VARCHAR2 DEFAULT ’ORACLE’,
· transform VARCHAR2 DEFAULT ’DDL’
下面建立了一個用於測試的EmpTest資料表，它帶有索引和約束：
create table EmpTest
(
empNo integer not null,
lastName varchar2(30) not null,
firstName varchar2(20) not null,
job varchar2(9) ’
hireDate date ’
isActive number(1)
constraint EmpTest_CK1
check (isActive in (0,1)) ,
salary number(9,2) ,
commision number(9,2) ,
deptNo number(2) ,
constraint EmpTest_PK
primary key (empNo),
constraint EmpTest_AK1
unique (lastName, firstName)
);
create index EmpTest_HireDate_Salary
on EmpTest
(
salary,
hireDate
);
運行上面的指令碼之後，就建立了一個帶有三個索引（兩個唯一的和一個不唯一的索引）的EmpTest表：
select index_name, index_type, uniqueness
from user_indexes
where table_name = ’EMPTEST’;
索引名稱索引類型唯一性EMPTEST_AK1NORMALUNIQUEEMPTEST_HIREDATE_SALARYNORMALNONUNIQUEEMPTEST_PKNORMALUNIQUE
EmpTest表還包括六個約束：
· 一個主鍵-EmpTest_PK
· 一個備用鍵-EmpTest_AK
· 一個檢查約束-EmpTest_CK1
· 系統產生的（SYS_*）三個非空的約束，名稱如下：
約束名稱約束類型索引名稱SYS_C002144065C SYS_C002144066C SYS_C002144067C EMPTEST_CK1C EMPTEST_PKP EMPTEST_PKEMPTEST_AK1U EMPTEST_AK1
現在我們執行匿名的PL/SQL代碼塊來調用DBMS_METADATA.GET_DDL函數，檢索資料表的定義。
DBMS_OUTPUT程式包只能輸出最長為255個字元的字串，由於在處理資料表的DDL字串的時候太容易超過這個限制，所以這是一個問題。為瞭解決這個問題，我們使用了本地過程Show()（列表1所示）。
列表1：調用DBMS_METADATA.GET_DDL()函數的PL/SQL代碼塊
declare
vClob clob;
vLongString varchar2(32767);
vOffSet pls_integer := 0;
vLength pls_integer := 0;
vTable varchar2(30) := ’EmpTest’;
procedure Show (pVariable varchar2, pLineSize pls_integer := 80)
is
begin
dbms_output.enable(1000000);
if (length(pVariable) > pLineSize)
then
dbms_output.put_line(substr(pVariable, 1, pLineSize));
Show(substr(pVariable, pLineSize + 1), pLineSize);
else
dbms_output.put_line(pVariable);
end if;
end Show;
begin
-- 擷取 DDL
vClob := dbms_metadata.get_ddl(’TABLE’, upper(vTable));
-- 擷取 CLOB 長度
vLength := dbms_lob.GetLength(vClob);
dbms_output.put_line(’DDL length: ’ || to_char(vLength));
vOffSet := 1;
dbms_lob.read(vClob, vLength, vOffSet, vLongString);
-- 關閉 CLOB
if (dbms_lob.isOpen(vClob) > 0)
then
dbms_lob.close(vClob);
end if;
Show(vLongString, 80);
end;
列表1產生下面的輸出資訊：
DDL length: 461
CREATE TABLE "BORIS"."EMPTEST"
( "EMPNO" NUMBER(*,0) NOT NULL ENABLE,
"LASTNAME" VARCHAR2(30) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
"JOB" VARCHAR2(9),
"HIREDATE" DATE,
"ISACTIVE" NUMBER(1,0),
"SALARY" NUMBER(9,2),
"COMMISION" NUMBER(9,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMPTEST_CK1" CHECK (isActive in (0,1)) ENABLE,
CONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TOOLS" ENABLE, CONSTRAINT "EMPTEST_AK1" UNIQUE ("LASTNAME", "FIRSTNAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TOOLS" ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TOOLS"
它啟動並執行情況太好了，返回的資料表的DDL字串帶有主鍵EmpTest_PK、備用鍵EmpTest_AK1和檢查約束EmpTest_CK1。它建立了兩個唯一的索引來支援主鍵和備用鍵約束。這不是你需要的結果：你需要一個表，但是為了加快資料載入速度，它不要包含約束和索引。只有在資料載入工作完成以後，你才建立索引和約束。
保證對象的定義獨立的另外一個原因在於靈活性：你可能需要改變對象建立的次序。
現在可以設計一個資料結構來儲存物件的中繼資料了。
[1] [2] [3] 下一頁
