Oracle中提取和存储数据库对象的DDL

1、Oracle中提取和存储数据库对象的DDL文章关键字：Oracle,提取,存储从对象（例如数据库表、索引、约束、触发器等）中提取DDL命令的普通方法涉及到的操作包括从这些对象中提取元数据（metadata），并把这些数据存储在内存中。尽管目前有很多脚本可以实现这样的功能，但是它们通常都是不完整的或者过时的。幸运的是，Oracle 9.2提供了一个实现这样的功能的API：DBMS_METADATA程序包。在很多情况下，数据库中数据的维护操作要求我们提取多种对象（例如数据库表、索引、约束、触发器等）的DDL（Data Definition Language，数据定义语言）。最近我承担了一个任务，我

2、需要编写一组数据库程序包来执行高性能的大量的数据删除（DELETE）操作。这样的操作要求我拥有提取和存储数据库对象DDL的相关技术。提取和存储数据库对象的DDL的方法如下： 建立与源表结构相同的数据表，但是它不带主键、备用键和外部键约束。 例如，使用MyTable_X，其中MyTable是要被删除的目标数据表。 把需要保存的数据插入新建立的数据表（MyTable_X）中。 使用NOLOGGING PARALLEL选项在新数据表上建立索引。 在新数据表上建立约束。 MyTable和MyTable_X数据表进行交换。把主表改名为MyTable_T，把MyTable_X改名为MyTable。 验证结

3、果并删除MyTable_T表。很明显，为了编写实现上面目标的代码，你必须提取数据库对象的元数据（定义和被选中的属性），并把它存储在内存中，这样在执行上面的操作的时候才能够使用它。在网上存在大量的脚本，它们可以从多种Oracle数据字典（user_tables、user_indexes、user_ind_columns、user_constraints、user_cons_columns等）中提取数据库对象的元数据，接着为特定的对象构造DDL命令。这些脚本的一个问题是，它们通常是SQL*Plus脚本，它会生成客户端文本文件，而这个文件不能被服务器端代码访问。它们的主要问题有： 不完整：不能提取所

4、有的选项，并组合进DDL语句中。 过时了：这些脚本通常不支持Oracle最新的数据库特性-分区（partitioning）、基于函数的索引、自动段空间管理（ASSM）等。这些脚本可能崩溃或生成错误的DDL语句。问题总结：尽管有大量的从Oracle数据字典中提取数据库对象元数据的脚本，但是它们中的大多数要么不完整，要么过期了。解决方案：使用DBMS_METADATA程序包，学习如何用最佳的、没有错误的和易于维护的方式执行上面的事务。使用Oracle的本地API：DBMS_METADATA程序包Oracle数据库采用补充PL/SQL程序包的形式提供了丰富的预先包装好的API。Oracle 9.2版

5、本中引入的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 下面建立了一个用于测试的Em

6、pTest数据表，它带有索引和约束：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_CK1check (isActive in (0,1) ,salary number(9,2) ,commision number(9,2) ,deptNo number(2) ,constraint EmpTes

7、t_PKprimary key (empNo),constraint EmpTest_AK1unique (lastName, firstName);create index EmpTest_HireDate_Salaryon EmpTest(salary,hireDate);运行上面的脚本之后，就建立了一个带有三个索引（两个唯一的和一个不唯一的索引）的EmpTest表：select index_name, index_type, uniquenessfrom user_indexeswhere table_name = EMPTEST;索引名称索引类型唯一性EMPTEST_AK1NORMAL

8、UNIQUEEMPTEST_HIREDATE_SALARYNORMALNONUNIQUEEMPTEST_PKNORMALUNIQUEEmpTest表还包括六个约束： 一个主键-EmpTest_PK 一个备用键-EmpTest_AK 一个检查约束-EmpTest_CK1 系统生成的（SYS_*）三个非空的约束，名称如下：约束名称约束类型索引名称SYS_CC SYS_CC SYS_CC EMPTEST_CK1C EMPTEST_PKP EMPTEST_PKEMPTEST_AK1U EMPTEST_AK1现在我们执行匿名的PL/SQL代码块来调用DBMS_METADATA.GET_DDL函数，检索数

9、据表的定义。DBMS_OUTPUT程序包只能输出最长为255个字符的字符串，由于在处理数据表的DDL字符串的时候太容易超过这个限制，所以这是一个问题。为了解决这个问题，我们使用了本地过程Show()（列表1所示）。列表1：调用DBMS_METADATA.GET_DDL()函数的PL/SQL代码块declarevClob clob;vLongString varchar2(32767);vOffSet pls_integer := 0;vLength pls_integer := 0; vTable varchar2(30) := EmpTest;procedure Show (pVariabl

10、e varchar2, pLineSize pls_integer := 80)isbegin dbms_output.enable(); 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- 获取 DDLvClob := dbms_metadata.

11、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);- 关闭 CLOBif (dbms_lob.isOpen(vClob) 0)thendbms_lob.close(vClob);end if;Show(vLongString, 80);end;列表1生成下面的

12、输出信息：DDL length: 461CREATE 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_C

13、K1 CHECK (isActive in (0,1) ENABLE, CONSTRAINT EMPTEST_PK PRIMARY KEY (EMPNO)USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT MINEXTENTS 1 MAXEXTENTS PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE TOOLS ENABLE, CONSTRAINT EMPTEST_AK1 UNIQUE (LASTN

14、AME, FIRSTNAME)USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT MINEXTENTS 1 MAXEXTENTS PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE TOOLS ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT MINEXTE

15、NTS 1 MAXEXTENTS PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE TOOLS它运行的情况太好了，返回的数据表的DDL字符串带有主键EmpTest_PK、备用键EmpTest_AK1和检查约束EmpTest_CK1。它建立了两个唯一的索引来支持主键和备用键约束。这不是你需要的结果：你需要一个表，但是为了加快数据载入速度，它不要包含约束和索引。只有在数据载入工作完成以后，你才建立索引和约束。保证对象的定义独立的另外一个原因在于灵活性：你可能需要改变对象建立的次序。 现在可以设计一个

16、数据结构来存储对象的元数据了。元数据存储器：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 tLongStringindex by pls_integer;type tMetaO

17、bject 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：对象

18、的日志选项，例如LOGGING/ NOLOGGING （用于表和索引）。 aParallel: 对象的平行程度（用于表和索引）。 AStatus：对象的状态，例如VALID/UNUSABLE 用于索引、 Y （备份了）/N （未备份）用于表。 AValidated：对象的验证选项，例如VALIDATED/NOT VALIDATED（用于约束）。 ARely：对象的依赖选项，例如RELY/NORELY （用于约束）。 ADDLString：对象的定义SQL字符串。现在你必须定义一个相关的数组类型，它能够列举出某种类型的对象，从保存tMetaObject类型的多个对象，例如，所有的EmpTest索

19、引：type tArrayMetaObject is table of tMetaObjectindex by pls_integer;下一步需要建立一个记录类型，它包含了数据表表自身（aTable）的tMetaObject属性和三个tArrayMetaObject属性：一个用于索引（aIndexes），一个用于约束（aConstraints），一个用于触发器（aTriggers）：type tFullMetaObject is record(aTable tMetaObject,aIndexes tArrayMetaObject,aConstraints tArrayMetaObject,a

20、Triggers tArrayMetaObject);tFullMetaObject对象类型保存了单个表的全部对象的元数据。最后，位于顶层的类型是tFullMetaObject数组。TarrayFullMetaObjectByString类型是tFullMetaObject的一个表，索引类型是varchar2(30)。列表2：MetaDataPkg程序包规范用如下的方式建立或更新MetaDataPkg：cEnabled constant char(7) := ENABLED;cDisabled constant char(8) := DISABLED;cUsable constant char

21、(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

22、;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) := SE

23、QUENCE;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

24、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

25、(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;cParalle

26、l 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 tDBStrin

27、g is varchar2(255);subtype tDBLongString is varchar2(4000);subtype tLongString is varchar2(32767); type tArrayLongString is table of tLongStringindex by pls_integer;type tMetaObject is record(aName tString,aType tString,aLogging tString,aParallel tString,aStatus tString,aValidated tString,aRely tStr

28、ing,aDDLString tLongString);type tArrayMetaObject is table of tMetaObjectindex by pls_integer;type tFullMetaObject is record(aTable tMetaObject,aIndexes tArrayMetaObject,aConstraints tArrayMetaObject,aTriggers tArrayMetaObject);type tArrayFullMetaObjectByString is table of tFullMetaObjectindex by va

29、rchar2(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 GetMetareturn tArrayFullMetaObjectByString;procedure SetMeta(pTable in tString,pFu

30、llMetaObject in tFullMetaObject);procedure SetMeta(pArrayFullMetaObjectByString in tArrayFullMetaObjectByString);procedure Show (pTable in tString);procedure Show; end MetaDataPkg;上面的类型对象是作为元数据存储器的，用于存放多个表的完整的元数据信息集合。所有上面的类型都包含在程序包规范中（列表2所示）。我还介绍了下面一些API： MetaDataPkg.Load()过程：把特定表的元数据信息载入存储器中。 MetaD

31、ataPkg.GetMeta()函数：它从存储器中检索tFullMetaObject类型的对象。 MetaDataPkg.SetMeta()过程（重载的）：把对象的元数据存储到存储器中。 MetaDataPkg.Reset过程（重载的）：对存储器复位。 MetaDataPkg.Show过程（重载的）：显示存储器的内容。实现所有这些事务的代码列表3显示了MetaDataPkg程序包主体代码的一些解释。私有过程SetEnvironment()包含了所有的环境设置代码。在程序包的初始化部分会调用这个过程，因此在每个对话中它都只执行一次，符合你的需求（你希望在开头设置一次）。程序包提供了用于设置环境参

32、数的API：DBMS_METADATA.SET_TRANSFORM_PARAM()过程。列表3：MetaDataPkg程序包主体vMetaData tArrayFullMetaObjectByString;procedure SetEnvironmentisbegindbms_metadata.set_transform_param(dbms_metadata.session_transform, PRETTY, false);dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_transform, SEGMENT_ATTRIBUTE

33、S, 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(db

34、ms_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)isvLineSize pls_integer := least(nvl(pLineSize, 80), 255);begindbms_outp

35、ut.enable();if (length(pString) vLineSize)thendbms_output.put_line(substr(pString, 1, vLineSize);Print(substr(pString, pLineSize + 1), vLineSize);elsedbms_output.put_line(pString);end if;end Print;procedure Show (pMetaObject in tMetaObject)isbegindbms_output.put_line(*);dbms_output.put_line(Name: |

36、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_ou

37、tput.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 tLongStringisvClob clob;vLongStrings tArrayLongString;vFullLength pls_integer := 0;vOffSet pls_integer := 0;vLength pls_

38、integer := 0;beginvClob := dbms_metadata.get_ddl(pType, upper(pName);vFullLength := dbms_lob.GetLength(vClob);for nIndex in 1.ceil(vFullLength / 32767)loopvOffSet := vLength + 1;vLength := least(vFullLength - (nIndex - 1) * 32767, 32767);dbms_lob.read(vClob, vLength, vOffSet, vLongStrings(nIndex);vL

39、ongStrings(nIndex) := replace(vLongStrings(nIndex),cDoubleQuote | user | cDoubleQuote | .,);vLongStrings(nIndex) :=ltrim(rtrim(replace(vLongStrings(nIndex), chr(10), ); end loop;if (dbms_lob.isOpen(vClob) 0)thendbms_lob.close(vClob);end if;return vLongStrings(1);end GetDDL;function ObjectExists(pObj

40、ectName in tString,pObjectType in tString,pTableName in tString := null)return booleanisvCount pls_integer := 0;vObjectName tString := ltrim(rtrim(pObjectName);vObjectType tString := upper(ltrim(rtrim(pObjectType);vTableName tString := upper(ltrim(rtrim(pTableName);begincasewhen vObjectType = cColum

41、nthenselect count(*)into vCountfrom Dualwhere exists (select 1from user_tab_columnswhere column_name = upper(vObjectName)and table_name = vTableName);when vObjectType = cConstraintthenselect count(*)into vCountfrom Dualwhere exists (select 1from user_constraintswhere constraint_name = upper(vObjectN

42、ame)and table_name = vTableName);when vObjectType in (cJavaSource, cJavaClass)thenselect count(*)into vCountfrom Dualwhere exists (select 1from user_objectswhere object_name = vObjectName and object_type = vObjectType);elseselect count(*)into vCountfrom Dualwhere exists (select 1from user_objectswhe

43、re object_name = upper(pObjectName)and object_type = vObjectType);end case;return (vCount 0);end ObjectExists;procedure Load(pTable in tString,pForce in boolean := false)isvFullMetaObject tFullMetaObject;vTable tString := upper(ltrim(rtrim(pTable);vCount pls_integer := 0;beginif (not vMetaData.exist

44、s(vTable) or nvl(pForce, false)thenif not ObjectExists(pTable, cTable)thenraise_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_upfrom user_tableswhere

45、table_name = vTable)loopvFullMetaObject.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.aD

46、DLString := GetDDL(rec.table_name, cTable);end loop; for rec in (select index_name,uniqueness,logging,ltrim(rtrim(degree) as degree,statusfrom user_indexeswhere table_name = vTableand index_type != cLobType)loopvCount := vCount + 1;vFullMetaObject.aIndexes(vCount).aName := rec.index_name;vFullMetaOb

47、ject.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, cInd

48、ex);end loop;vCount := 0;for rec in (select constraint_name,constraint_type,status,search_condition,validated,rely from user_constraints where table_name = vTableorder by decode(constraint_type,cPKConsType, 10,cUNConsType, 20,cFKConsType, 30,cCKConsType, 40,100),constraint_name)loop vCount := vCount

49、 + 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.st

50、atus;vFullMetaObject.aConstraints(vCount).aValidated := rec.validated;vFullMetaObject.aConstraints(vCount).aRely := rec.rely;if substr(rec.constraint_name, 1, length(cSYSPrefix) = cSYSPrefix andupper(rec.search_condition) like %IS | cNotNull | %thenvFullMetaObject.aConstraints(vCount).aDDLString :=

51、ALTER TABLE | cDoubleQuote | vFullMetaObject.aTable.aName | cDoubleQuote | |MODIFY | replace(rec.search_condition, IS | cNotNull, cNotNull) |(case when vFullMetaObject.aConstraints(vCount).aValidated = cNotValidatedthen | cNovalidateelse end);elsevFullMetaObject.aConstraints(vCount).aDDLString := Ge

52、tDDL(rec.constraint_name, cConstraint);end if;end loop;SetMeta(pTable, vFullMetaObject); end if;end Load;procedure Reset(pTable in tString)isbeginvMetaData.delete(pTable);end Reset;procedure ResetisbeginvMetaData.delete;end Reset;function GetMeta(pTable in tString,pForce in boolean := false)return tFullMetaObjectisbeginif (not vMetaData.exists(pTable) or nvl(pForce, false)thenLoad(pTable, pForce);if not vMetaData.exists(pTable)thenraise_application_error(-20501, Unable to find