Shulou(Shulou.com)06/01 Report--

The case of Oracle Study-- DBMS_METADATA Package Application

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.

Case study:

[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 DEFAULTParameters

Table 87-8 GET_xxx Function Parameters

ParameterDescription

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.

Using dbms_metadata.get_ddl Procedure to analyze the object properties of creating table in different ways of extent management (Local or Dictionary)

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

From the above, we can see that "USERS tablespace" extent management is local mode, and "DICT1 tablespace" extent management is dictionary mode.

1) get emp table information (stored in USERS table space)

14:44:14 SCOTT@ test1 > set long 500014 set linesize 4435 SCOTT@ test1 > set linesize 14014 44 SCOTT@ test1 > set pagesize 100014 set linesize 4454 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 INITRANS 0), "HIREDATE" DATE, "SAL" NUMBER (7 Magazine 2), "COMM" NUMBER (7 PK_EMP 2), "DEPTNO" NUMBER (2 PK_EMP 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 255NOCOMPRESS 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) get T1 table information (stored in DICT1 tablespace)

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 255NOCOMPRESS 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) View the data dictionary

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

Judging from the above, for the table stored on the tablespace managed by local, Oracle allocates an extent with a size of 64k by default, enabling the pct_free parameter, while the pct_used and freelists parameters are no longer used. For table stored on the tablespace managed by dictionary, Oracle assigns a 40k extent by default when creating it, and the pct_free,pct_used,freelists parameter is enabled.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.