Oracle Study之案例--DBMS_METADATA Package应用

re21

DBMS_METADATA：
      The DBMS_METADATA package 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

1 2 3 4 5 6 7 8 9

FUNCTION GET_DDL RETURNS CLOB Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- OBJECT_TYPE                    VARCHAR2                IN NAME                           VARCHAR2                IN SCHEMA                         VARCHAR2                IN     DEFAULT VERSION                        VARCHAR2                IN     DEFAULT MODEL                          VARCHAR2                IN     DEFAULT TRANSFORM                      VARCHAR2                IN     DEFAULT

Parameters

Table 87-8 GET_xxx Function Parameters

Parameter

Description

object_type

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

name

The object name. It is used internally in a NAME filter. (If the name is longer than 30 characters, it will be used in a LONGNAME filter.) If this parameter is NULL, then no NAME or LONGNAME filter is specifiedSee Table 87-17 for a list of filters.

schema

The object schema. It is used internally in a SCHEMA filter. The default is the current user.

version

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

model

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

transform

The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.

通过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;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

TABLESPACE_NAME                CONTENTS  EXTENT_MAN ------------------------------ --------- ---------- SYSTEM                         PERMANENT DICTIONARY SYSAUX                         PERMANENT LOCAL TEMP1                          TEMPORARY LOCAL USERS                          PERMANENT LOCAL UNDOTBS2                       UNDO      LOCAL TEMP2                          TEMPORARY LOCAL INDX                           PERMANENT LOCAL TMP3                           TEMPORARY LOCAL TMP4                           TEMPORARY LOCAL TEST1                          PERMANENT LOCAL PERFS                          PERMANENT LOCAL DICT1                          PERMANENT DICTIONARY TBS_16                         PERMANENT LOCAL

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

14:44:14 SCOTT@ test1 >set long 5000 14:44:35 SCOTT@ test1 >set linesize 140 14:44:47 SCOTT@ test1 >set pagesize 1000 14:44:54 SCOTT@ test1 >select dbms_metadata.get_ddl('TABLE','EMP') FROM DUAL DBMS_METADATA.GET_DDL('TABLE','EMP') --------------------------------------------------------------------------------   CREATE TABLE "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" PRIMARY KEY ("EMPNO")   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP FLASH_CACHE DEFAULT CE LL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"  ENABLE,          CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")           REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE    ) SEGMENT CREATION IMMEDIATE   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 RECYCLE FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" 14:44:55 SCOTT@ test1 >select dbms_metadata.get_ddl('INDEX','PK_EMP') FROM DUAL; DBMS_METADATA.GET_DDL('INDEX','PK_EMP') --------------------------------------------------------------------------------   CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"

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

1 2 3 4 5 6 7 8 9 10 11 12

14:52:12 SCOTT@ test1 >select dbms_metadata.get_ddl('TABLE','T1') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','T1') --------------------------------------------------------------------------------   CREATE TABLE "SCOTT"."T1"    (    "ID" NUMBER(*,0),         "NAME" VARCHAR2(10)    ) SEGMENT CREATION IMMEDIATE   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505   PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEF AULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "DICT1"

3）查看数据字典

1 2 3 4 5 6

14:58:23 SCOTT@ test1 >select table_name,TABLESPACE_NAME,PCT_FREE,PCT_USED,FREELISTS,INITIAL_EXTENT/1024,NEXT_EXTENT/1024 from user_tables; TABLE_NAME TABLESPACE_NAME                  PCT_FREE   PCT_USED  FREELISTS INITIAL_EXTENT/1024 NEXT_EXTENT/1024 ---------- ------------------------------ ---------- ---------- ---------- ------------------- ---------------- EMP        USERS                                  10                                        64             1024 T1         DICT1                                  10         40          1                  40               40

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