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 |
|---|---|
|
|
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;
|
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参数。
