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

从对象（例如数据库表、索引、约束、触发器等）中提取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

SYS_C002144066

SYS_C002144067

EMPTEST_CK1

EMPTEST_PK

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)

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

begin