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

Oracle中提取和存储数据库对象的DDL文章关键字：Oracle,提取,存储从对象（例如数据库表、索引、约束、触发器等）中提取DDL命令的普通方法涉及到的操作包括从这些对象中提取元数据（metadata），并把这些数据存储在内存中。尽管目前有很多脚本可以实现这样的功能，但是它们通常都是不完整的或者过时的。幸运的是，Oracle9.2提供了一个实现这样的功能的API：DBMS_METADATA程序包。在很多情况下，数据库中数据的维护操作要求我们提取多种对象（例如数据库表、索引、约束、触发器等）的DDL（DataDefinitionLanguage，数据定义语言）。最近我承担了一个任务，我需要编写一组数据库程序包来执行高性能的大量的数据删除（DELETE）操作。这样的操作要求我拥有提取和存储数据库对象DDL的相关技术。提取和存储数据库对象的DDL的方法如下：·建立与源表结构相同的数据表，但是它不带主键、备用键和外部键约束。·例如，使用MyTable_X，其中MyTable是要被删除的目标数据表。·把需要保存的数据插入新建立的数据表（MyTable_X）中。·使用NOLOGGINGPARALLEL选项在新数据表上建立索引。·在新数据表上建立约束。·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。Oracle9.2版本中引入的DBMS_METADATA程序包可能正好适合你的需求。它包含了用于检索数据库对象定义的API。我们将使用的API主要是DBMS_METADATA.GET_DDL函数。这个函数返回的对象定义SQL字符串是CLOB。它拥有下面一些输入参数：·object_typeVARCHAR2·nameVARCHAR2·schemaVARCHAR2DEFAULTNULL·versionVARCHAR2DEFAULT’COMPATIBLE’·modelVARCHAR2DEFAULT’ORACLE’,·transformVARCHAR2DEFAULT’DDL’下面建立了一个用于测试的EmpTest数据表，它带有索引和约束：createtableEmpTest(empNointegernotnull,lastNamevarchar2(30)notnull,firstNamevarchar2(20)notnull,jobvarchar2(9)’hireDatedate’isActivenumber(1)constraintEmpTest_CK1check(isActivein(0,1)),salarynumber(9,2),commisionnumber(9,2),deptNonumber(2),constraintEmpTest_PKprimarykey(empNo),constraintEmpTest_AK1unique(lastName,firstName));createindexEmpTest_HireDate_SalaryonEmpTest(salary,hireDate);运行上面的脚本之后，就建立了一个带有三个索引（两个唯一的和一个不唯一的索引）的EmpTest表：selectindex_name,index_type,uniquenessfromuser_indexeswheretable_name=’EMPTEST’;索引名称索引类型唯一性EMPTEST_AK1NORMALUNIQUEEMPTEST_HIREDATE_SALARYNORMALNONUNIQUEEMPTEST_PKNORMALUNIQUEEmpTest表还包括六个约束：·一个主键-EmpTest_PK·一个备用键-EmpTest_AK·一个检查约束-EmpTest_CK1·系统生成的（SYS_*）三个非空的约束，名称如下：约束名称约束类型索引名称SYS_C002144065CSYS_C002144066CSYS_C002144067CEMPTEST_CK1CEMPTEST_PKPEMPTEST_PKEMPTEST_AK1UEMPTEST_AK1现在我们执行匿名的PL/SQL代码块来调用DBMS_METADATA.GET_DDL函数，检索数据表的定义。DBMS_OUTPUT程序包只能输出最长为255个字符的字符串，由于在处理数据表的DDL字符串的时候太容易超过这个限制，所以这是一个问题。为了解决这个问题，我们使用了本地过程Show()（列表1所示）。列表1：调用DBMS_METADATA.GET_DDL()函数的PL/SQL代码块declarevClobclob;vLongStringvarchar2(32767);vOffSetpls_integer:=0;vLengthpls_integer:=0;vTablevarchar2(30):=’EmpTest’;procedureShow(pVariablevarchar2,pLineSizepls_integer:=80)isbegindbms_output.enable(1000000);if(length(pVariable)pLineSize)thendbms_output.put_line(substr(pVariable,1,pLineSize));Show(substr(pVariable,pLineSize+1),pLineSize);elsedbms_output.put_line(pVariable);endif;endShow;begin--获取DDLvClob:=dbms_metadata.get_ddl(’TABLE’,upper(vTable));--获取CLOB长度vLength:=dbms_lob.GetLength(vClob);dbms_output.put_line(’DDLlength:’||to_char(vLength));vOffSet:=1;dbms_lob.read(vClob,vLength,vOffSet,vLongString);--关闭CLOBif(dbms_lob.isOpen(vClob)0)thendbms_lob.close(vClob);endif;Show(vLongString,80);end;列表1生成下面的输出信息：DDLlength:461CREATETABLEBORIS.EMPTEST(EMPNONUMBER(*,0)NOTNULLENABLE,LASTNAMEVARCHAR2(30)NOTNULLENABLE,FIRSTNAMEVARCHAR2(20)NOTNULLENABLE,JOBVARCHAR2(9),HIREDATEDATE,ISACTIVENUMBER(1,0),SALARYNUMBER(9,2),COMMISIONNUMBER(9,2),DEPTNONUMBER(2,0),CONSTRAINTEMPTEST_CK1CHECK(isActivein(0,1))ENABLE,CONSTRAINTEMPTEST_PKPRIMARYKEY(EMPNO)USINGINDEXPCTFREE10INITRANS2MAXTRANS255STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)TABLESPACETOOLSENABLE,CONSTRAINTEMPTEST_AK1UNIQUE(LASTNAME,FIRSTNAME)USINGINDEXPCTFREE10INITRANS2MAXTRANS255STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)TABLESPACETOOLSENABLE)PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)TABLESPACETOOLS它运行的情况太好了，返回的数据表的DDL字符串带有主键EmpTest_PK、备用键EmpTest_AK1和检查约束EmpTest_CK1。它建立了两个唯一的索引来支持主键和备用键约束。这不是你需要的结果：你需要一个表，但是为了加快数据载入速度，它不要包含约束和索引。只有在数据载入工作完成以后，你才建立索引和约束。保证对象的定义独立的另外一个原因在于灵活性：你可能需要改变对象建立的次序。现在可以设计一个数据结构来存储对象的元数据了。元数据存储器：MetaDataPkg程序包规范首先，你必须建立记录类型来存储独立的对象（例如数据表、索引等）的所有必要信息：subtypetStringisvarchar2(30);subtypetDBStringisvarchar2(255);subtypetDBLongStringisvarchar2(4000);subtypetLongStringisvarchar2(32767);typetArrayLongStringistableoftLongStringindexbypls_integer;typetMetaObjectisrecord(aNametString,aTypetString,aLoggingtString,aParalleltString,aStatustString,aValidatedtString,aRelytString,aDDLStringtLongString);tMetaObject属性保存了下面一些信息：·aName：对象的名称，例如EMPTEST_PK1。·aType：对象的类型，例如’YES’（分区的）/’NO’（分区的）（用于表）、’UNIQUE’/’NONUNIQUE’（用于索引）、约束类型’P’/’U’/’C’/’R’（用于约束）。·aLogging：对象的日志选项，例如’LOGGING’/’NOLOGGING’（用于表和索引）。·aParallel:对象的平行程度（用于