從對(duì)象（例如數(shù)據(jù)庫(kù)表、索引、約束、觸發(fā)器等）中提取DDL命令的普通方法涉及到的操作包括從這些對(duì)象中提取元數(shù)據(jù)（metadata），并把這些數(shù)據(jù)存儲(chǔ)在內(nèi)存中。盡管目前有很多腳本可以實(shí)現(xiàn)這樣的功能，但是它們通常都是不完整的或者過(guò)時(shí)的。 幸運(yùn)的是，Oracle 9.2提供了一個(gè)實(shí)現(xiàn)這樣的功能的API：DBMS_METADATA程序包。
在很多情況下，數(shù)據(jù)庫(kù)中數(shù)據(jù)的維護(hù)操作要求我們提取多種對(duì)象（例如數(shù)據(jù)庫(kù)表、索引、約束、觸發(fā)器等）的DDL（Data Definition Language，數(shù)據(jù)定義語(yǔ)言）。
最近我承擔(dān)了一個(gè)任務(wù)，我需要編寫一組數(shù)據(jù)庫(kù)程序包來(lái)執(zhí)行高性能的大量的數(shù)據(jù)刪除（DELETE）操作。這樣的操作要求我擁有提取和存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象DDL的相關(guān)技術(shù)。
提取和存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象的DDL的方法如下：
· 建立與源表結(jié)構(gòu)相同的數(shù)據(jù)表，但是它不帶主鍵、備用鍵和外部鍵約束。
· 例如，使用MyTable_X，其中MyTable是要被刪除的目標(biāo)數(shù)據(jù)表。
· 把需要保存的數(shù)據(jù)插入新建立的數(shù)據(jù)表（MyTable_X）中。
· 使用NOLOGGING PARALLEL選項(xiàng)在新數(shù)據(jù)表上建立索引。
· 在新數(shù)據(jù)表上建立約束。
· MyTable和MyTable_X數(shù)據(jù)表進(jìn)行交換。把主表改名為MyTable_T，把MyTable_X改名為MyTable。
· 驗(yàn)證結(jié)果并刪除MyTable_T表。
很明顯，為了編寫實(shí)現(xiàn)上面目標(biāo)的代碼，你必須提取數(shù)據(jù)庫(kù)對(duì)象的元數(shù)據(jù)（定義和被選中的屬性），并把它存儲(chǔ)在內(nèi)存中，這樣在執(zhí)行上面的操作的時(shí)候才能夠使用它。
在網(wǎng)上存在大量的腳本，它們可以從多種Oracle數(shù)據(jù)字典（user_tables、user_indexes、user_ind_columns、user_constraints、user_cons_columns等）中提取數(shù)據(jù)庫(kù)對(duì)象的元數(shù)據(jù)，接著為特定的對(duì)象構(gòu)造DDL命令。這些腳本的一個(gè)問(wèn)題是，它們通常是SQL*Plus腳本，它會(huì)生成客戶端文本文件，而這個(gè)文件不能被服務(wù)器端代碼訪問(wèn)。它們的主要問(wèn)題有：
· 不完整：不能提取所有的選項(xiàng)，并組合進(jìn)DDL語(yǔ)句中。
· 過(guò)時(shí)了：這些腳本通常不支持Oracle最新的數(shù)據(jù)庫(kù)特性--分區(qū)（partitioning）、基于函數(shù)的索引、自動(dòng)段空間治理（ASSM）等。這些腳本可能崩潰或生成錯(cuò)誤的DDL語(yǔ)句。
問(wèn)題總結(jié)：盡管有大量的從Oracle數(shù)據(jù)字典中提取數(shù)據(jù)庫(kù)對(duì)象元數(shù)據(jù)的腳本，但是它們中的大多數(shù)要么不完整，要么過(guò)期了。
解決方案：使用DBMS_METADATA程序包，學(xué)習(xí)如何用最佳的、沒有錯(cuò)誤的和易于維護(hù)的方式執(zhí)行上面的事務(wù)。
使用Oracle的本地API：DBMS_METADATA程序包
Oracle數(shù)據(jù)庫(kù)采用補(bǔ)充PL/SQL程序包的形式提供了豐富的預(yù)先包裝好的API。Oracle 9.2版本中引入的DBMS_METADATA程序包可能正好適合你的需求。它包含了用于檢索數(shù)據(jù)庫(kù)對(duì)象定義的API。
我們將使用的API主要是DBMS_METADATA.GET_DDL函數(shù)。這個(gè)函數(shù)返回的對(duì)象定義SQL字符串是CLOB。它擁有下面一些輸入?yún)?shù)：
· object_type VARCHAR2
· name VARCHAR2
· schema VARCHAR2 DEFAULT NULL
· version VARCHAR2 DEFAULT ’COMPATIBLE’
· model VARCHAR2 DEFAULT ’ORACLE’,
· transform VARCHAR2 DEFAULT ’DDL’
下面建立了一個(gè)用于測(cè)試的EmpTest數(shù)據(jù)表，它帶有索引和約束：
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
);
運(yùn)行上面的腳本之后，就建立了一個(gè)帶有三個(gè)索引（兩個(gè)唯一的和一個(gè)不唯一的索引）的EmpTest表：
select index_name, index_type, uniqueness
from user_indexes
where table_name = ’EMPTEST’;
索引名稱索引類型唯一性EMPTEST_AK1NORMALUNIQUEEMPTEST_HIREDATE_SALARYNORMALNONUNIQUEEMPTEST_PKNORMALUNIQUE
EmpTest表還包括六個(gè)約束：
· 一個(gè)主鍵-EmpTest_PK
· 一個(gè)備用鍵-EmpTest_AK
· 一個(gè)檢查約束-EmpTest_CK1
· 系統(tǒng)生成的（SYS_*）三個(gè)非空的約束，名稱如下：
約束名稱約束類型索引名稱SYS_C002144065C SYS_C002144066C SYS_C002144067C EMPTEST_CK1C EMPTEST_PKP EMPTEST_PKEMPTEST_AK1U EMPTEST_AK1
現(xiàn)在我們執(zhí)行匿名的PL/SQL代碼塊來(lái)調(diào)用DBMS_METADATA.GET_DDL函數(shù)，檢索數(shù)據(jù)表的定義。
DBMS_OUTPUT程序包只能輸出最長(zhǎng)為255個(gè)字符的字符串，由于在處理數(shù)據(jù)表的DDL字符串的時(shí)候太輕易超過(guò)這個(gè)限制，所以這是一個(gè)問(wèn)題。為了解決這個(gè)問(wèn)題，我們使用了本地過(guò)程Show()（列表1所示）。
列表1：調(diào)用DBMS_METADATA.GET_DDL()函數(shù)的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 長(zhǎng)度
vLength := dbms_lob.GetLength(vClob);
dbms_output.put_line(’DDL length: ’ to_char(vLength));
vOffSet := 1;
dbms_lob.read(vClob, vLength, vOffSet, vLongString);
-- 關(guān)閉 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"
它運(yùn)行的情況太好了，返回的數(shù)據(jù)表的DDL字符串帶有主鍵EmpTest_PK、備用鍵EmpTest_AK1和檢查約束EmpTest_CK1。它建立了兩個(gè)唯一的索引來(lái)支持主鍵和備用鍵約束。這不是你需要的結(jié)果：你需要一個(gè)表，但是為了加快數(shù)據(jù)載入速度，它不要包含約束和索引。只有在數(shù)據(jù)載入工作完成以后，你才建立索引和約束。
保證對(duì)象的定義獨(dú)立的另外一個(gè)原因在于靈活性：你可能需要改變對(duì)象建立的次序。
現(xiàn)在可以設(shè)計(jì)一個(gè)數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)對(duì)象的元數(shù)據(jù)了。
元數(shù)據(jù)存儲(chǔ)器：MetaDataPkg程序包規(guī)范
首先，你必須建立記錄類型來(lái)存儲(chǔ)獨(dú)立的對(duì)象（例如數(shù)據(jù)表、索引等）的所有必要信息：
suBType tString is varchar2(30);
subtype tDBString is varchar2(255);
subtype tDBLongString is varchar2(4000);
subtype tLongString is varchar2(32767);
type tArrayLongString is table of tLongString
index by pls_integer;
type tMetaObject is record
(
aName tString,
aType tString,
aLogging tString,
aParallel tString,
aStatus tString,
aValidated tString,
aRely tString,
aDDLString tLongString
);
tMetaObject屬性保存了下面一些信息：
· aName：對(duì)象的名稱，例如EMPTEST_PK1。
· aType：對(duì)象的類型，例如’YES’ （分區(qū)的）/’NO’ （分區(qū)的） （用于表）、 ’UNIQUE’/’NONUNIQUE’ （用于索引）、 約束類型 ’P’/’U’/’C’/’R’ （用于約束）。
· aLogging：對(duì)象的日志選項(xiàng)，例如’LOGGING’/ ’NOLOGGING’ （用于表和索引）。
· aParallel: 對(duì)象的平行程度（用于表和索引）。
· AStatus：對(duì)象的狀態(tài)，例如’VALID’/’UNUSABLE’ 用于索引、 ’Y’ （備份了）/’N’ （未備份）用于表。
· AValidated：對(duì)象的驗(yàn)證選項(xiàng)，例如’VALIDATED’/’NOT VALIDATED’（用于約束）。
· ARely：對(duì)象的依靠選項(xiàng)，例如’RELY’/’NORELY’ （用于約束）。
· ADDLString：對(duì)象的定義SQL字符串。
現(xiàn)在你必須定義一個(gè)相關(guān)的數(shù)組類型，它能夠列舉出某種類型的對(duì)象，從保存tMetaObject類型的多個(gè)對(duì)象，例如，所有的EmpTest索引：
type tArrayMetaObject is table of tMetaObject
index by pls_integer;
下一步需要建立一個(gè)記錄類型，它包含了數(shù)據(jù)表表自身（aTable）的tMetaObject屬性和三個(gè)tArrayMetaObject屬性：一個(gè)用于索引（aIndexes），一個(gè)用于約束（aConstraints），一個(gè)用于觸發(fā)器（aTriggers）：
type tFullMetaObject is record
(
aTable tMetaObject,
aIndexes tArrayMetaObject,
aConstraints tArrayMetaObject,
aTriggers tArrayMetaObject
);
tFullMetaObject對(duì)象類型保存了單個(gè)表的全部對(duì)象的元數(shù)據(jù)。最后，位于頂層的類型是tFullMetaObject數(shù)組。TarrayFullMetaObjectByString類型是tFullMetaObject的一個(gè)表，索引類型是varchar2(30)。
列表2：MetaDataPkg程序包規(guī)范
用如下的方式建立或更新MetaDataPkg：
cEnabled constant char(7) := ’ENABLED’;
cDisabled constant char(8) := ’DISABLED’;
cUsable constant char(6) := ’USABLE’;
cUnusable constant char(8) := ’UNUSABLE’;
cValid constant char(5) := ’VALID’;
cInvalid constant char(7) := ’INVALID’;
cTable constant char(5) := ’TABLE’;
cView constant char(4) := ’VIEW’;
cIndex constant char(5) := ’INDEX’;
cConstraint constant char(10) := ’CONSTRAINT’;
cTrigger constant char(7) := ’TRIGGER’;
cLobType constant char(3) := ’LOB’;
cClobType constant char(4) := ’CLOB’;
cBlobType constant char(4) := ’BLOB’;
cPackage constant char(7) := ’PACKAGE’;
cPackageBody constant char(12) := ’PACKAGE BODY’;
cProcedure constant char(9) := ’PROCEDURE’;
cFunction constant char(8) := ’FUNCTION’;
cSequence constant char(8) := ’SEQUENCE’;
cSynonym constant char(7) := ’SYNONYM’;
cType constant char(4) := ’TYPE’;
cColumn constant char(6) := ’COLUMN’;
cjavaSource constant char(11) := ’JAVA SOURCE’;
cJavaClass constant char(10) := ’JAVA CLASS’;
cYes constant char(3) := ’YES’;
cNo constant char(2) := ’NO’;
cPKConsType constant char(1) := ’P’;
cUNConsType constant char(1) := ’U’;
cFKConsType constant char(1) := ’R’;
cCKConsType constant char(1) := ’C’;
cDropStorage constant char(12) := ’DROP STORAGE’;
cReuseStorage constant char(13) := ’REUSE STORAGE’;
cCascade constant char(19) := ’CASCADE CONSTRAINTS’;
cNoCascade constant char(10) := ’NO CASCADE’;
cEnable constant char(6) := ’ENABLE’;
cNovalidate constant char(10) := ’NOVALIDATE’;
cRely constant char(4) := ’RELY’;
cNoRely constant char(6) := ’NORELY’;
cValidated constant char(9) := ’VALIDATED’;
cNotValidated constant char(13) := ’NOT VALIDATED’;
cLogging constant char(7) := ’LOGGING’;
cNoLogging constant char(9) := ’NOLOGGING’;
cParallel constant char(8) := ’PARALLEL’;
cNoParallel constant char(10) := ’NOPARALLEL’;
cNull constant char(4) := ’NULL’;
cNotNull constant char(8) := ’NOT NULL’;
cDefault constant char(7) := ’DEFAULT’;
cSYSPrefix constant char(4) := ’SYS_’;
cDoubleQuote constant char(1) := ’"’;
subtype tString is varchar2(30);
subtype tDBString is varchar2(255);
subtype tDBLongString is varchar2(4000);
subtype tLongString is varchar2(32767);
type tArrayLongString is table of tLongString
index by pls_integer;
type tMetaObject is record
(
aName tString,
aType tString,
aLogging tString,
aParallel tString,
aStatus tString,
aValidated tString,
aRely tString,
aDDLString tLongString
);
type tArrayMetaObject is table of tMetaObject
index by pls_integer;
type tFullMetaObject is record
(
aTable tMetaObject,
aIndexes tArrayMetaObject,
aConstraints tArrayMetaObject,
aTriggers tArrayMetaObject
);
type tArrayFullMetaObjectByString is table of tFullMetaObject
index by varchar2(30);
procedure Load
(
pTable in tString,
pForce in boolean := false
);
procedure Reset
(
pTable in tString
);
procedure Reset;
function GetMeta
(
pTable in tString,
pForce in boolean := false
)
return tFullMetaObject;
function GetMeta
return tArrayFullMetaObjectByString;
procedure SetMeta
(
pTable in tString,
pFullMetaObject in tFullMetaObject
);
procedure SetMeta
(
pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
);
procedure Show
(
pTable in tString
);
procedure Show;
end MetaDataPkg;
上面的類型對(duì)象是作為元數(shù)據(jù)存儲(chǔ)器的，用于存放多個(gè)表的完整的元數(shù)據(jù)信息集合。所有上面的類型都包含在程序包規(guī)范中（列表2所示）。我還介紹了下面一些API：
· MetaDataPkg.Load()過(guò)程：把特定表的元數(shù)據(jù)信息載入存儲(chǔ)器中。
· MetaDataPkg.GetMeta()函數(shù)：它從存儲(chǔ)器中檢索tFullMetaObject類型的對(duì)象。
· MetaDataPkg.SetMeta()過(guò)程（重載的）：把對(duì)象的元數(shù)據(jù)存儲(chǔ)到存儲(chǔ)器中。
· MetaDataPkg.Reset過(guò)程（重載的）：對(duì)存儲(chǔ)器復(fù)位。
· MetaDataPkg.Show過(guò)程（重載的）：顯示存儲(chǔ)器的內(nèi)容。
實(shí)現(xiàn)所有這些事務(wù)的代碼
列表3顯示了MetaDataPkg程序包主體代碼的一些解釋。私有過(guò)程SetEnvironment()包含了所有的環(huán)境設(shè)置代碼。在程序包的初始化部分會(huì)調(diào)用這個(gè)過(guò)程，因此在每個(gè)對(duì)話中它都只執(zhí)行一次，符合你的需求（你希望在開頭設(shè)置一次）。程序包提供了用于設(shè)置環(huán)境參數(shù)的API：DBMS_METADATA.SET_TRANSFORM_PARAM()過(guò)程。
列表3：MetaDataPkg程序包主體
vMetaData tArrayFullMetaObjectByString;
procedure SetEnvironment
is
begin
dbms_metadata.set_transform_param(
dbms_metadata.session_transform, ’PRETTY’, false);
dbms_metadata.SET_TRANSFORM_PARAM(
dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);
dbms_metadata.set_transform_param(
dbms_metadata.session_transform, ’STORAGE’, true);
dbms_metadata.set_transform_param(
dbms_metadata.session_transform, ’TABLESPACE’, true);
dbms_metadata.set_transform_param(
dbms_metadata.session_transform, ’CONSTRAINTS’, false);
dbms_metadata.set_transform_param(
dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);
dbms_metadata.set_transform_param(
dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);
end SetEnvironment;
procedure Print
(
pString varchar2,
pLineSize positive := 80
)
is
vLineSize pls_integer := least(nvl(pLineSize, 80), 255);
begin
dbms_output.enable(1000000);
if (length(pString) > vLineSize)
then
dbms_output.put_line(substr(pString, 1, vLineSize));
Print(substr(pString, pLineSize + 1), vLineSize);
else
dbms_output.put_line(pString);
end if;
end Print;
procedure Show
(
pMetaObject in tMetaObject
)
is
begin
dbms_output.put_line(’***’);
dbms_output.put_line(’Name: ’ pMetaObject.aName);
dbms_output.put_line(’Type: ’ pMetaObject.aType);
dbms_output.put_line(’Logging: ’ pMetaObject.aLogging);
dbms_output.put_line(’Parallel: ’
to_char(pMetaObject.aParallel));
dbms_output.put_line(’Status: ’ pMetaObject.aStatus);
dbms_output.put_line(’Validated: ’
pMetaObject.aValidated);
dbms_output.put_line(’Rely: ’ pMetaObject.aRely);
print(’DDL String: ’ pMetaObject.aDDLString, 255);
dbms_output.put_line(’***’);
end Show;
function GetDDL
(
pName in tString,
pType in tString
)
return tLongString
is
vClob clob;
vLongStrings tArrayLongString;
vFullLength pls_integer := 0;
vOffSet pls_integer := 0;
vLength pls_integer := 0;
begin
vClob := dbms_metadata.get_ddl(pType, upper(pName));
vFullLength := dbms_lob.GetLength(vClob);
for nIndex in 1..ceil(vFullLength / 32767)
loop
vOffSet := vLength + 1;
vLength := least(vFullLength - (nIndex - 1) * 32767, 32767);
dbms_lob.read(vClob, vLength, vOffSet, vLongStrings(nIndex));
vLongStrings(nIndex) := replace(vLongStrings(nIndex),
cDoubleQuote user cDoubleQuote ’.’,
’’);
vLongStrings(nIndex) :=ltrim(rtrim(replace(vLongStrings(nIndex), chr(10), ’’)));
end loop;
if (dbms_lob.isOpen(vClob) > 0)
then
dbms_lob.close(vClob);
end if;
return vLongStrings(1);
end GetDDL;
function ObjectExists
(
pObjectName in tString,
pObjectType in tString,
pTableName in tString := null
)
return boolean
is
vCount pls_integer := 0;
vObjectName tString := ltrim(rtrim(pObjectName));
vObjectType tString := upper(ltrim(rtrim(pObjectType)));
vTableName tString := upper(ltrim(rtrim(pTableName)));
begin
case
when vObjectType = cColumn
then
select count(*)
into vCount
from Dual
where exists (select ’1’
from user_tab_columns
where column_name = upper(vObjectName)
and table_name = vTableName);
when vObjectType = cConstraint
then
select count(*)
into vCount
from Dual
where exists (select ’1’
from user_constraints
where constraint_name = upper(vObjectName)
and table_name = vTableName);
when vObjectType in (cJavaSource, cJavaClass)
then
select count(*)
into vCount
from Dual
where exists (select ’1’
from user_objects
where object_name = vObjectName and object_type = vObjectType);
else
select count(*)
into vCount
from Dual
where exists (select ’1’
from user_objects
where object_name = upper(pObjectName)
and object_type = vObjectType);
end case;
return (vCount > 0);
end ObjectExists;
procedure Load
(
pTable in tString,
pForce in boolean := false
)
is
vFullMetaObject tFullMetaObject;
vTable tString := upper(ltrim(rtrim(pTable)));
vCount pls_integer := 0;
begin
if (not vMetaData.exists(vTable) or nvl(pForce, false))
then
if not ObjectExists(pTable, cTable)
then
raise_application_error(-20500,
’Unable to load metadata for ’ nvl(pTable, ’NULL’)
’. ’ ’Table does not exist.’
);
end if;
for rec in (select table_name,
logging,
ltrim(rtrim(degree)) as degree,
partitioned,
backed_up
from user_tables
where table_name = vTable)
loop
vFullMetaObject.aTable.aName := rec.table_name;
vFullMetaObject.aTable.aType := rec.partitioned;
vFullMetaObject.aTable.aLogging := rec.logging;
vFullMetaObject.aTable.aParallel := ltrim(rtrim(rec.degree));
vFullMetaObject.aTable.aStatus := rec.backed_up;
vFullMetaObject.aTable.aDDLString := GetDDL(rec.table_name, cTable);
end loop;
for rec in (select index_name,
uniqueness,
logging,
ltrim(rtrim(degree)) as degree,
status
from user_indexes
where table_name = vTable
and index_type != cLobType)
loop
vCount := vCount + 1;
vFullMetaObject.aIndexes(vCount).aName := rec.index_name;
vFullMetaObject.aIndexes(vCount).aType := rec.uniqueness;
vFullMetaObject.aIndexes(vCount).aLogging := rec.logging;
vFullMetaObject.aIndexes(vCount).aParallel :=
ltrim(rtrim(rec.degree));
vFullMetaObject.aIndexes(vCount).aStatus := rec.status;
vFullMetaObject.aIndexes(vCount).aDDLString :=
GetDDL(rec.index_name, cIndex);
end loop;
vCount := 0;
for rec in (select constraint_name,constraint_type,status,search_condition,validated,rely from user_constraints where table_name = vTable
order by decode(constraint_type,
cPKConsType, 10,
cUNConsType, 20,
cFKConsType, 30,
cCKConsType, 40,
100),
constraint_name)
loop
vCount := vCount + 1;
vFullMetaObject.aConstraints(vCount).aName := rec.constraint_name;
vFullMetaObject.aConstraints(vCount).aType := rec.constraint_type;
vFullMetaObject.aConstraints(vCount).aLogging := null;
vFullMetaObject.aConstraints(vCount).aParallel := null;
vFullMetaObject.aConstraints(vCount).aStatus := rec.status;
vFullMetaObject.aConstraints(vCount).aValidated := rec.validated;
vFullMetaObject.aConstraints(vCount).aRely := rec.rely;
if substr(rec.constraint_name, 1, length(cSYSPrefix)) = cSYSPrefix and
upper(rec.search_condition) like ’%IS ’ cNotNull ’%’
then
vFullMetaObject.aConstraints(vCount).aDDLString :=
’ALTER TABLE ’ cDoubleQuote vFullMetaObject.aTable.aName cDoubleQuote ’ ’
’MODIFY ’ replace(rec.search_condition, ’IS ’ cNotNull, cNotNull)
(case when vFullMetaObject.aConstraints(vCount).aValidated = cNotValidated
then ’ ’ cNovalidate
else ’’end);
else
vFullMetaObject.aConstraints(vCount).aDDLString := GetDDL(rec.constraint_name, cConstraint);
end if;
end loop;
SetMeta(pTable, vFullMetaObject);
end if;
end Load;
procedure Reset
(
pTable in tString
)
is
begin
vMetaData.delete(pTable);
end Reset;
procedure Reset
is
begin
vMetaData.delete;
end Reset;
function GetMeta
(
pTable in tString,
pForce in boolean := false
)
return tFullMetaObject
is
begin
if (not vMetaData.exists(pTable) or nvl(pForce, false))
then
Load(pTable, pForce);
if not vMetaData.exists(pTable)
then
raise_application_error(-20501, ’Unable to find metadata for ’ pTable ’ in repository.’);
end if;
end if;
return vMetaData(pTable);
end GetMeta;
function GetMeta
return tArrayFullMetaObjectByString
is
begin
return vMetaData;
end GetMeta;
procedure SetMeta
(
pTable in tString,
pFullMetaObject in tFullMetaObject
)
is
begin
vMetaData(pTable) := pFullMetaObject;
end SetMeta;
procedure SetMeta
(
pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
)
is
begin
vMetaData := pArrayFullMetaObjectByString;
end SetMeta;
procedure Show
(
pTable in tString
)
is
vFullMetaObject tFullMetaObject;
begin
if (vMetaData.exists(pTable))
then
dbms_output.enable(1000000);
vFullMetaObject := vMetaData(pTable);
dbms_output.put_line(’Start Full Object: ’ pTable);
dbms_output.put_line(’Start Table: ’ pTable);
Show(vFullMetaObject.aTable);
dbms_output.put_line(’Finish Table: ’ pTable);
dbms_output.put_line(’Start Indexes: ’ pTable);
if (vFullMetaObject.aIndexes.count > 0)
then
for nIndex in vFullMetaObject.aIndexes.first..vFullMetaObject.aIndexes.last
loop
Show(vFullMetaObject.aIndexes(nIndex));
end loop;
end if;
dbms_output.put_line(’Finish Indexes: ’ pTable);
dbms_output.put_line(’Start Constraints: ’ pTable);
if (vFullMetaObject.aConstraints.count > 0)
then
for nIndex in vFullMetaObject.aConstraints.first..vFullMetaObject.aConstraints.last
loop
Show(vFullMetaObject.aConstraints(nIndex));
end loop;
end if;
dbms_output.put_line(’Finish Constraints: ’ pTable);
dbms_output.put_line(’Start Triggers: ’ pTable);
if (vFullMetaObject.aTriggers.count > 0)
then
for nIndex in vFullMetaObject.aTriggers.first..vFullMetaObject.aTriggers.last
loop
Show(vFullMetaObject.aTriggers(nIndex));
end loop;
end if;
dbms_output.put_line(’Finish Triggers: ’ pTable);
dbms_output.put_line(’Finish Full Object: ’ pTable);
end if;
end Show;
procedure Show
is
vTable tString;
begin
if vMetaData.count > 0
then
dbms_output.put_line(’Total Meta Objects: ’ to_char(vMetaData.count));
vTable := vMetaData.first;
while (vTable is not null)
loop
Show(vTable);
vTable := vMetaData.next(vTable);
end loop;
end if;
end Show;
begin
SetEnvironment;
end MetaDataPkg;
下面的代碼防止輸出信息采用縮排或換行格式化：
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’PRETTY’, false);
下面的三行輸出片段屬性（物理屬性、存儲(chǔ)屬性、表空間、日志等）、數(shù)據(jù)表的存儲(chǔ)、表空間子句和索引對(duì)象定義：
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’STORAGE’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’TABLESPACE’, true);
明確地指定所有的物理、存儲(chǔ)和日志屬性是非常重要的--否則，它們會(huì)被設(shè)為默認(rèn)值，而這個(gè)值可能與原始設(shè)置的值不同。
SetEnvironment()過(guò)程最后的三行防止所有的非參考和參考約束被包含到表的DDL中。它還禁止獨(dú)立的ALTER TABLE語(yǔ)句（假如必要，還可以禁止CREATE INDEX語(yǔ)句）來(lái)生成數(shù)據(jù)表約束：
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);
為了達(dá)到最大的靈活性，最好分別提取數(shù)據(jù)表、索引和約束的對(duì)象定義并保證它們彼此都相互獨(dú)立。通過(guò)這種辦法，你可以控制這些對(duì)象的建立次序。
MetaDataPkg程序包的主要工作部分是MetaDataPkg.GetDDL()函數(shù)。MetaDataPkg.GetDDL()包含了列表1代碼的擴(kuò)展版本。添加到里面的是提取超過(guò)32767個(gè)字符的DDL字符串的能力。它可以幫助處理分區(qū)的數(shù)據(jù)表定義--隨著分區(qū)數(shù)量的增長(zhǎng)，它可能變得很長(zhǎng)。這也是GetDDL()代碼把DDL字符串分析并載入每個(gè)長(zhǎng)達(dá)32767字符的字符串?dāng)?shù)組的原因。目前的代碼版本只返回第一個(gè)數(shù)組元素，因此你需要修改這段代碼，把該數(shù)組轉(zhuǎn)換為tMetaObject記錄類型的屬性。這樣就答應(yīng)它處理長(zhǎng)于32767字符的字符串，當(dāng)然這種情況非常少見。
使用MetaDataPkg.GetMeta() API可以得到每個(gè)特定數(shù)據(jù)表的完整的元數(shù)據(jù)對(duì)象。這個(gè)API接受兩個(gè)參數(shù)：pTable，它是表的名稱；pForce，布爾型標(biāo)記。當(dāng)pForce被設(shè)置為TRUE的時(shí)候，它強(qiáng)迫元數(shù)據(jù)從Oracle數(shù)據(jù)字典中檢索，接著把元數(shù)據(jù)載入存儲(chǔ)器中--不管是否預(yù)備好了。但是默認(rèn)的值是FALSE，因此第一個(gè)調(diào)用把元數(shù)據(jù)載入存儲(chǔ)器中并返回tFullMetaObject類型的對(duì)象，后面的GetMeta()調(diào)用簡(jiǎn)單地從存儲(chǔ)器中檢索元數(shù)據(jù)。
使用MetaDataPkg程序包
為了演示如何使用MetaDataPkg程序包，我建立了一小段匿名代碼塊。它把EmpTest表中的元數(shù)據(jù)載入元數(shù)據(jù)存儲(chǔ)器中，并輸出它的內(nèi)容。
下面就是匿名的PL/SQL代碼塊：
declare
vTable MetaDataPkg.tString := ’EmpTest’;
vRunStartTime number;
begin
vRunStartTime := dbms_utility.get_time;
MetaDataPkg.Load(vTable, true);
MetaDataPkg.Show();
dbms_output.put_line(’Time Elapsed: ’
to_char((dbms_utility.get_time - vRunStartTime) / 100) ’ sec.’);
end;
列表4顯示了前面的代碼的輸出信息。
你可以看到，這段代碼把EmpTest數(shù)據(jù)表和其索引、約束的全部元數(shù)據(jù)信息載入到存儲(chǔ)器中，并在一秒鐘之內(nèi)把它檢索出來(lái)了。你現(xiàn)在擁有了一個(gè)用于開發(fā)自動(dòng)的解決方案的API了，它可以進(jìn)行任何數(shù)據(jù)維護(hù)操作，包括更名、轉(zhuǎn)換和刪除數(shù)據(jù)庫(kù)對(duì)象。
