Oracle Study之案例--DBMS_METADATA Package应用
Oracle Study之案例--DBMS_METADATA Package应用
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
| Parameter | Description |
|---|---|
| The type of object to be retrieved. This parameter takes the same values as the |
| The object name. It is used internally in a |
| The object schema. It is used internally in a |
| The version of metadata to be extracted. This parameter takes the same values as the |
| The object model to use. This parameter takes the same values as the |
| The name of a transformation on the output. This parameter takes the same values as the |
通过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_NAME CONTENTS EXTENT_MAN------------------------------ --------- ----------SYSTEM PERMANENT DICTIONARYSYSAUX PERMANENT LOCALTEMP1 TEMPORARY LOCALUSERS PERMANENT LOCALUNDOTBS2 UNDO LOCALTEMP2 TEMPORARY LOCALINDX PERMANENT LOCALTMP3 TEMPORARY LOCALTMP4 TEMPORARY LOCALTEST1 PERMANENT LOCALPERFS PERMANENT LOCALDICT1 PERMANENT DICTIONARYTBS_16 PERMANENT LOCAL
从以上可以看到"USERS tablespace"extent management 为local方式，"DICT1 tablespace" extent management 为dictionary方式
1）获取emp表信息（存储在USERS表空间）
14:44:14 SCOTT@ test1 >set long 500014:44:35 SCOTT@ test1 >set linesize 14014:44:47 SCOTT@ test1 >set pagesize 100014:44:54 SCOTT@ test1 >select dbms_metadata.get_ddl('TABLE','EMP') FROM DUALDBMS_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 CELL_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 DEFAULTCELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"2）获取t1表信息（存储在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 DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DICT1"3）查看数据字典
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 1024T1 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参数。
