Oracle Study之案例--DBMS_METADATA Package应用

DBMS_METADATA：

TheDBMS_METADATApackage provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

案例分析：

[oracle@RH6 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 8 14:36:29 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:36:29 SYS@ test1 >desc dbms_metadata

FUNCTIONGET_DDLRETURNSCLOBArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------OBJECT_TYPEVARCHAR2INNAMEVARCHAR2INSCHEMAVARCHAR2INDEFAULTVERSIONVARCHAR2INDEFAULTMODELVARCHAR2INDEFAULTTRANSFORMVARCHAR2INDEFAULT

Parameters

Table 87-8 GET_xxx Function Parameters

object_type

The type of object to be retrieved. This parameter takes the same values as theOPEN object_typeparameter, except that it cannot be a heterogeneous object type. The attributes of the object type must be appropriate to the function. That is, forGET_xxxit must be a named object.

name

The object name. It is used internally in aNAMEfilter. (If the name is longer than 30 characters, it will be used in aLONGNAMEfilter.) If this parameter is NULL, then noNAMEorLONGNAMEfilter is specifiedSeeTable 87-17for a list of filters.

schema

The object schema. It is used internally in aSCHEMAfilter. The default is the current user.

version

The version of metadata to be extracted. This parameter takes the same values as theOPENversion parameter.

model

The object model to use. This parameter takes the same values as theOPENmodel parameter.

transform

The name of a transformation on the output. This parameter takes the same values as theADD_TRANSFORMnameparameter. ForGET_XMLthis must not beDDL.

通过dbms_metadata.get_ddl Procedure分析在extent management（Local or Dictionary）不同方式下创建table的对象属性

14:50:43 SYS@ test1 >select tablespace_name,contents,extent_management from dba_tablespaces;

TABLESPACE_NAMECONTENTSEXTENT_MAN-------------------------------------------------SYSTEMPERMANENTDICTIONARYSYSAUXPERMANENTLOCALTEMP1TEMPORARYLOCALUSERSPERMANENTLOCALUNDOTBS2UNDOLOCALTEMP2TEMPORARYLOCALINDXPERMANENTLOCALTMP3TEMPORARYLOCALTMP4TEMPORARYLOCALTEST1PERMANENTLOCALPERFSPERMANENTLOCALDICT1PERMANENTDICTIONARYTBS_16PERMANENTLOCAL

从以上可以看到“USERS tablespace”extent management 为local方式，"DICT1 tablespace" extent management为dictionary方式

1）获取emp表信息（存储在USERS表空间）

14:44:14SCOTT@test1>setlong500014:44:35SCOTT@test1>setlinesize14014:44:47SCOTT@test1>setpagesize100014:44:54SCOTT@test1>selectdbms_metadata.get_ddl('TABLE','EMP')FROMDUALDBMS_METADATA.GET_DDL('TABLE','EMP')--------------------------------------------------------------------------------CREATETABLE"SCOTT"."EMP"("EMPNO"NUMBER(4,0),"ENAME"VARCHAR2(10),"JOB"VARCHAR2(9),"MGR"NUMBER(4,0),"HIREDATE"DATE,"SAL"NUMBER(7,2),"COMM"NUMBER(7,2),"DEPTNO"NUMBER(2,0),CONSTRAINT"PK_EMP"PRIMARYKEY("EMPNO")USINGINDEXPCTFREE10INITRANS2MAXTRANS255COMPUTESTATISTICSSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLKEEPFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"USERS"ENABLE,CONSTRAINT"FK_DEPTNO"FOREIGNKEY("DEPTNO")REFERENCES"SCOTT"."DEPT"("DEPTNO")ENABLE)SEGMENTCREATIONIMMEDIATEPCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLRECYCLEFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"USERS"14:44:55SCOTT@test1>selectdbms_metadata.get_ddl('INDEX','PK_EMP')FROMDUAL;DBMS_METADATA.GET_DDL('INDEX','PK_EMP')--------------------------------------------------------------------------------CREATEUNIQUEINDEX"SCOTT"."PK_EMP"ON"SCOTT"."EMP"("EMPNO")PCTFREE10INITRANS2MAXTRANS255COMPUTESTATISTICSSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLKEEPFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"USERS"

2）获取t1表信息（存储在DICT1表空间）

14:52:12SCOTT@test1>selectdbms_metadata.get_ddl('TABLE','T1')FROMDUAL;DBMS_METADATA.GET_DDL('TABLE','T1')--------------------------------------------------------------------------------CREATETABLE"SCOTT"."T1"("ID"NUMBER(*,0),"NAME"VARCHAR2(10))SEGMENTCREATIONIMMEDIATEPCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL40960NEXT40960MINEXTENTS1MAXEXTENTS505PCTINCREASE50FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"DICT1"

3）查看数据字典

14:58:23SCOTT@test1>selecttable_name,TABLESPACE_NAME,PCT_FREE,PCT_USED,FREELISTS,INITIAL_EXTENT/1024,NEXT_EXTENT/1024fromuser_tables;TABLE_NAMETABLESPACE_NAMEPCT_FREEPCT_USEDFREELISTSINITIAL_EXTENT/1024NEXT_EXTENT/1024---------------------------------------------------------------------------------------------------------EMPUSERS10641024T1DICT1104014040

从以上可以判断，对于存储在local管理的tablespace上的table，在创建时，Oracle默认会分配大小64k的extent，启用pct_free参数，而pct_used和freelists参数不再被使用。对于存储在dictionary管理的tablespace上的table，在创建时，Oracle默认会分配大小40k得extent，启用pct_free,pct_used,freelists参数。