Oracle Study之案例--DBMS_METADATA Package應(yīng)用

Oracle Study之案例--DBMS_METADATA Package應(yīng)用

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

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

通過(guò)dbms_metadata.get_ddl Procedure分析在extent management（Local or  Dictionary）不同方式下創(chuàng)建table的對(duì)象屬性

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

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表信息（存儲(chǔ)在USERS表空間）

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表信息（存儲(chǔ)在DICT1表空間）

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）查看數(shù)據(jù)字典

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

    從以上可以判斷，對(duì)于存儲(chǔ)在local管理的tablespace上的table，在創(chuàng)建時(shí)，Oracle默認(rèn)會(huì)分配大小64k的extent，啟用pct_free參數(shù)，而pct_used和freelists參數(shù)不再被使用。對(duì)于存儲(chǔ)在dictionary管理的tablespace上的table，在創(chuàng)建時(shí)，Oracle默認(rèn)會(huì)分配大小40k得extent，啟用pct_free,pct_used,freelists參數(shù)。