Oracle中提取和存储数据库对象的DDL.docx
- 文档编号:5963576
- 上传时间:2023-01-02
- 格式:DOCX
- 页数:26
- 大小:25.66KB
Oracle中提取和存储数据库对象的DDL.docx
《Oracle中提取和存储数据库对象的DDL.docx》由会员分享,可在线阅读,更多相关《Oracle中提取和存储数据库对象的DDL.docx(26页珍藏版)》请在冰豆网上搜索。
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_CK1
check(isActivein(0,1)),
salarynumber(9,2),
commisionnumber(9,2),
deptNonumber
(2),
constraintEmpTest_PK
primarykey(empNo),
constraintEmpTest_AK1
unique(lastName,firstName)
);
createindexEmpTest_HireDate_Salary
onEmpTest
(
salary,
hireDate
);
运行上面的脚本之后,就建立了一个带有三个索引(两个唯一的和一个不唯一的索引)的EmpTest表:
selectindex_name,index_type,uniqueness
fromuser_indexes
wheretable_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
vClobclob;
vLongStringvarchar2(32767);
vOffSetpls_integer:
=0;
vLengthpls_integer:
=0;
vTablevarchar2(30):
=’EmpTest’;
procedureShow(pVariablevarchar2,pLineSizepls_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);
endif;
endShow;
begin
--获取DDL
vClob:
=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);
--关闭CLOB
if(dbms_lob.isOpen(vClob)>0)
then
dbms_lob.close(vClob);
endif;
Show(vLongString,80);
end;
列表1生成下面的输出信息:
DDLlength:
461
CREATETABLE"BORIS"."EMPTEST"
("EMPNO"NUMBER(*,0)NOTNULLENABLE,
"LASTNAME"VARCHAR2(30)NOTNULLENABLE,
"FIRSTNAME"VARCHAR2(20)NOTNULLENABLE,
"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(isActivein(0,1))ENABLE,
CONSTRAINT"EMPTEST_PK"PRIMARYKEY("EMPNO")
USINGINDEXPCTFREE10INITRANS2MAXTRANS255
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE"TOOLS"ENABLE,CONSTRAINT"EMPTEST_AK1"UNIQUE("LASTNAME","FIRSTNAME")
USINGINDEXPCTFREE10INITRANS2MAXTRANS255
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE"TOOLS"ENABLE)PCTFREE10PCTUSED40INITRANS1
MAXTRANS255NOCOMPRESSLOGGING
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
TABLESPACE"TOOLS"
它运行的情况太好了,返回的数据表的DDL字符串带有主键EmpTest_PK、备用键EmpTest_AK1和检查约束EmpTest_CK1。
它建立了两个唯一的索引来支持主键和备用键约束。
这不是你需要的结果:
你需要一个表,但是为了加快数据载入速度,它不要包含约束和索引。
只有在数据载入工作完成以后,你才建立索引和约束。
保证对象的定义独立的另外一个原因在于灵活性:
你可能需要改变对象建立的次序。
现在可以设计一个数据结构来存储对象的元数据了。
元数据存储器:
MetaDataPkg程序包规范
首先,你必须建立记录类型来存储独立的对象(例如数据表、索引等)的所有必要信息:
subtypetStringisvarchar2(30);
subtypetDBStringisvarchar2(255);
subtypetDBLongStringisvarchar2(4000);
subtypetLongStringisvarchar2(32767);
typetArrayLongStringistableoftLongString
indexbypls_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:
对象的平行程度(用于表和索引)。
·AStatus:
对象的状态,例如’VALID’/’UNUSABLE’用于索引、’Y’(备份了)/’N’(未备份)用于表。
·AValidated:
对象的验证选项,例如’VALIDATED’/’NOTVALIDATED’(用于约束)。
·ARely:
对象的依赖选项,例如’RELY’/’NORELY’(用于约束)。
·ADDLString:
对象的定义SQL字符串。
现在你必须定义一个相关的数组类型,它能够列举出某种类型的对象,从保存tMetaObject类型的多个对象,例如,所有的EmpTest索引:
typetArrayMetaObjectistableoftMetaObject
indexbypls_integer;
下一步需要建立一个记录类型,它包含了数据表表自身(aTable)的tMetaObject属性和三个tArrayMetaObject属性:
一个用于索引(aIndexes),一个用于约束(aConstraints),一个用于触发器(aTriggers):
typetFullMetaObjectisrecord
(
aTabletMetaObject,
aIndexestArrayMetaObject,
aConstraintstArrayMetaObject,
aTriggerstArrayMetaObject
);
tFullMetaObject对象类型保存了单个表的全部对象的元数据。
最后,位于顶层的类型是tFullMetaObject数组。
TarrayFullMetaObjectByString类型是tFullMetaObject的一个表,索引类型是varchar2(30)。
列表2:
MetaDataPkg程序包规范
用如下的方式建立或更新MetaDataPkg:
cEnabledconstantchar(7):
=’ENABLED’;
cDisabledconstantchar(8):
=’DISABLED’;
cUsableconstantchar(6):
=’USABLE’;
cUnusableconstantchar(8):
=’UNUSABLE’;
cValidconstantchar(5):
=’VALID’;
cInvalidconstantchar(7):
=’INVALID’;
cTableconstantchar(5):
=’TABLE’;
cViewconstantchar(4):
=’VIEW’;
cIndexconstantchar(5):
=’INDEX’;
cConstraintconstantchar(10):
=’CONSTRAINT’;
cTriggerconstantchar(7):
=’TRIGGER’;
cLobTypeconstantchar(3):
=’LOB’;
cClobTypeconstantchar(4):
=’CLOB’;
cBlobTypeconstantchar(4):
=’BLOB’;
cPackageconstantchar(7):
=’PACKAGE’;
cPackageBodyconstantchar(12):
=’PACKAGEBODY’;
cProcedureconstantchar(9):
=’PROCEDURE’;
cFunctionconstantchar(8):
=’FUNCTION’;
cSequenceconstantchar(8):
=’SEQUENCE’;
cSynonymconstantchar(7):
=’SYNONYM’;
cTypeconstantchar(4):
=’TYPE’;
cColumnconstantchar(6):
=’COLUMN’;
cJavaSourceconstantchar(11):
=’JAVASOURCE’;
cJavaClassconstantchar(10):
=’JAVACLASS’;
cYesconstantchar(3):
=’YES’;
cNoconstantchar
(2):
=’NO’;
cPKConsTypeconstantchar
(1):
=’P’;
cUNConsTypeconstantchar
(1):
=’U’;
cFKConsTypeconstantchar
(1):
=’R’;
cCKConsTypeconstantchar
(1):
=’C’;
cDropStorageconstantchar(12):
=’DROPSTORAGE’;
cReuseStorageconstantchar(13):
=’REUSESTORAGE’;
cCascadeconstantchar(19):
=’CASCADECONSTRAINTS’;
cNoCascadeconstantchar(10):
=’NOCASCADE’;
cEnableconstantchar(6):
=’ENABLE’;
cNovalidateconstantchar(10):
=’NOVALIDATE’;
cRelyconstantchar(4):
=’RELY’;
cNoRelyconstantchar(6):
=’NORELY’;
cValidatedconstantchar(9):
=’VALIDATED’;
cNotValidatedconstantchar(13):
=’NOTVALIDATED’;
cLoggingconstantchar(7):
=’LOGGING’;
cNoLoggingconstantchar(9):
=’NOLOGGING’;
cParallelconstantchar(8):
=’PARALLEL’;
cNoParallelconstantchar(10):
=’NOPARALLEL’;
cNullconstantchar(4):
=’NULL’;
cNotNullconstantchar(8):
=’NOTNULL’;
cDefaultconstantchar(7):
=’DEFAULT’;
cSYSPrefixconstantchar(4):
=’SYS_’;
cDoubleQuoteconstantchar
(1):
=’"’;
subtypetStringisvarchar2(30);
subtypetDBStringisvarchar2(255);
subtypetDBLongStringisvarchar2(4000);
subtypetLongStringisvarchar2(32767);
typetArrayLongStringistableoftLongString
indexbypls_integer;
typetMetaObjectisrecord
(
aNametString,
aTypetString,
aLoggingtString,
aParalleltString,
aStatustString,
aValidatedtString,
aRelytString,
aDDLStringtLongString
);
typetArrayMetaObjectistableoftMetaObject
indexbypls_integer;
typetFullMetaObjectisrecord
(
aTabletMetaObject,
aIndexestArrayMetaObject,
aConstraintstArrayMetaObject,
aTriggerstArrayMetaObject
);
typetArrayFullMetaObjectByStringistableoftFullMetaObject
indexbyvarchar2(30);
procedureLoad
(
pTableintString,
pForceinboolean:
=false
);
procedureReset
(
pTableintString
);
procedureReset;
functionGetMeta
(
pTableintString,
pForceinboolean:
=false
)
returntFullMetaObject;
functionGetMeta
returntArrayFullMetaObjectByString;
procedureSetMeta
(
pTableintString,
pFullMetaObject
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 提取 存储 数据库 对象 DDL