从对象（例如数据库表、索引、约束、触发器等）中提取DDL命令的普通方法涉及到的操作包括从这些对象中提取元数据（metadata），并把这些数据存储在内存中。尽管目前有很多脚本可以实现这样的功能，但是它们通常都是不完整的或者过时的。幸运的是，Oracle 9.2提供了一个实现这样的功能的API：DBMS_METADATA程序包。

　　在很多情况下，数据库中数据的维护操作要求我们提取多种对象（例如数据库表、索引、约束、触发器等）的DDL（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_AK1	NORMAL	UNIQUE
EMPTEST_HIREDATE_SALARY	NORMAL	NONUNIQUE
EMPTEST_PK	NORMAL	UNIQUE

　　EmpTest表还包括六个约束：

　　· 一个主键-EmpTest_PK

　　· 一个备用键-EmpTest_AK

　　· 一个检查约束-EmpTest_CK1

　　· 系统生成的（SYS_*）三个非空的约束，名称如下：

约束名称	约束类型	索引名称
SYS_C002144065	C
SYS_C002144066	C
SYS_C002144067	C
EMPTEST_CK1	C
EMPTEST_PK	P	EMPTEST_PK
EMPTEST_AK1	U	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。它建立了两个唯一的索引来支持主键和备用键约束。这不是你需要的结果：你需要一个表，但是为了加快数据载入速度，它不要包含约束和索引。只有在数据载入工作完成以后，你才建立索引和约束。

　　保证对象的定义独立的另外一个原因在于灵活性：你可能需要改变对象建立的次序。

　　现在可以设计一个数据结构来存储对象的元数据了。 元数据存储器：MetaDataPkg程序包规范

　　首先，你必须建立记录类型来存储独立的对象（例如数据表、索引等）的所有必要信息：

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：对象的名称，例如EMPTEST_PK1。

　　· aType：对象的类型，例如’YES’ （分区的）/’NO’ （分区的） （用于表）、 ’UNIQUE’/’NONUNIQUE’ （用于索引）、 约束类型 ’P’/’U’/’C’/’R’ （用于约束）。

　　· aLogging：对象的日志选项，例如’LOGGING’/ ’NOLOGGING’ （用于表和索引）。

　　· aParallel: 对象的平行程度（用于表和索引）。

　　· AStatus：对象的状态，例如’VALID’/’UNUSABLE’ 用于索引、 ’Y’ （备份了）/’N’ （未备份）用于表。

　　· AValidated：对象的验证选项，例如’VALIDATED’/’NOT VALIDATED’（用于约束）。

　　· ARely：对象的依赖选项，例如’RELY’/’NORELY’ （用于约束）。

　　· ADDLString：对象的定义SQL字符串。

　　现在你必须定义一个相关的数组类型，它能够列举出某种类型的对象，从保存tMetaObject类型的多个对象，例如，所有的EmpTest索引：

type tArrayMetaObject is table of tMetaObject index by pls_integer;

　　下一步需要建立一个记录类型，它包含了数据表表自身（aTable）的tMetaObject属性和三个tArrayMetaObject属性：一个用于索引（aIndexes），一个用于约束（aConstraints），一个用于触发器（aTriggers）：

type tFullMetaObject is record ( aTable tMetaObject, aIndexes tArrayMetaObject, aConstraints tArrayMetaObject, aTriggers tArrayMetaObject );

　　tFullMetaObject对象类型保存了单个表的全部对象的元数据。最后，位于顶层的类型是tFullMetaObject数组。TarrayFullMetaObjectByString类型是tFullMetaObject的一个表，索引类型是varchar2(30)。

　　列表2：MetaDataPkg程序包规范

　　用如下的方式建立或更新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;

　　上面的类型对象是作为元数据存储器的，用于存放多个表的完整的元数据信息集合。所有上面的类型都包含在程序包规范中（列表2所示）。我还介绍了下面一些API：

　　· MetaDataPkg.Load()过程：把特定表的元数据信息载入存储器中。

　　· MetaDataPkg.GetMeta()函数：它从存储器中检索tFullMetaObject类型的对象。

　　· MetaDataPkg.SetMeta()过程（重载的）：把对象的元数据存储到存储器中。

　　· MetaDataPkg.Reset过程（重载的）：对存储器复位。

　　· MetaDataPkg.Show过程（重载的）：显示存储器的内容。 实现所有这些事务的代码

　　列表3显示了MetaDataPkg程序包主体代码的一些解释。私有过程SetEnvironment()包含了所有的环境设置代码。在程序包的初始化部分会调用这个过程，因此在每个对话中它都只执行一次，符合你的需求（你希望在开头设置一次）。程序包提供了用于设置环境参数的API：DBMS_METADATA.SET_TRANSFORM_PARAM()过程。

　　列表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);

　　下面的三行输出片段属性（物理属性、存储属性、表空间、日志等）、数据表的存储、表空间子句和索引对象定义：

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);

　　明确地指定所有的物理、存储和日志属性是非常重要的--否则，它们会被设为默认值，而这个值可能与原始设置的值不同。

　　SetEnvironment()过程最后的三行防止所有的非参考和参考约束被包含到表的DDL中。它还禁止独立的ALTER TABLE语句（如果必要，还可以禁止CREATE INDEX语句）来生成数据表约束：

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);

　　为了达到最大的灵活性，最好分别提取数据表、索引和约束的对象定义并保证它们彼此都相互独立。通过这种办法，你可以控制这些对象的建立次序。

　　MetaDataPkg程序包的主要工作部分是MetaDataPkg.GetDDL()函数。MetaDataPkg.GetDDL()包含了列表1代码的扩展版本。添加到里面的是提取超过32767个字符的DDL字符串的能力。它可以帮助处理分区的数据表定义--随着分区数量的增长，它可能变得很长。这也是GetDDL()代码把DDL字符串分析并载入每个长达32767字符的字符串数组的原因。目前的代码版本只返回第一个数组元素，因此你需要修改这段代码，把该数组转换为tMetaObject记录类型的属性。这样就允许它处理长于32767字符的字符串，当然这种情况非常少见。

　　使用MetaDataPkg.GetMeta() API可以得到每个特定数据表的完整的元数据对象。这个API接受两个参数：pTable，它是表的名称；pForce，布尔型标记。当pForce被设置为TRUE的时候，它强迫元数据从Oracle数据字典中检索，接着把元数据载入存储器中--不管是否准备好了。但是默认的值是FALSE，因此第一个调用把元数据载入存储器中并返回tFullMetaObject类型的对象，后面的GetMeta()调用简单地从存储器中检索元数据。

　　使用MetaDataPkg程序包

　　为了演示如何使用MetaDataPkg程序包，我建立了一小段匿名代码块。它把EmpTest表中的元数据载入元数据存储器中，并输出它的内容。

　　下面就是匿名的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数据表和其索引、约束的全部元数据信息载入到存储器中，并在一秒钟之内把它检索出来了。你现在拥有了一个用于开发自动的解决方案的API了，它可以进行任何数据维护操作，包括更名、转换和删除数据库对象。

查看本文来源