获取表DDL的几个方法(即dbms_metadata包的使用）

http://blog.itpub.net/4227/viewspace-483803/

在9i以前，没有什么太好的方法，一般是通过查询数据字典，然后自己根据语法来拼接成一个完整的DDL语句，缺点是十分麻烦，需要用户对DDL的语法十分熟悉，而且经常容易缺失一些关键字。最重要的是，查询表的DDL与查询视图的DDL的方法就完全不一样，要获取多种对象的DDL，就需要为每种DDL来单独构造一个SQL。

此外，还有一种方法，就是利用Oracle的EXP工具。在导入的时候指定INDEXFILE，使得导入工具将DDL写入到一个文件中。这种方法的好处是不需要用户自己来拼接DDL了，而且支持多种类型的DDL，缺点也很明显，得到的结果无法直接使用，需要手工进行编辑，而编辑过程本身就很麻烦，会消耗大量的时间。

从9i开始，这个问题变得很轻松了，只需要一个查询语句，就可以获取对象的详细的DDL描述，上面所有的缺点完全被客服了：

语法：

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
DBMS_METADATA.GET_XML (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
DBMS_METADATA.OPEN (
object_type  IN VARCHAR2,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
model        IN VARCHAR2 DEFAULT 'ORACLE', 
network_link IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

示例：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------

CREATE TABLE "YANGTK"."T"
( "ID" NUMBER
) 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 DEFAULT)
TABLESPACE "YANGTK"

除了这个最常用的方法之外，利用DBMS_METADATA.OPEN的方式也可以达到相同的效果：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
2   V_STR CLOB;
3   V_HANDLE NUMBER;
4   V_TRANS_HANDLE NUMBER;
5 BEGIN
6   V_HANDLE := DBMS_METADATA.OPEN('TABLE');
7   DBMS_METADATA.SET_FILTER(V_HANDLE, 'NAME', 'T', 'TABLE');
8   V_TRANS_HANDLE := DBMS_METADATA.ADD_TRANSFORM(V_HANDLE, 'DDL');
9   V_STR := DBMS_METADATA.FETCH_CLOB(V_HANDLE);
10   FOR I IN 1..CEIL(DBMS_LOB.GETLENGTH(V_STR)/200) LOOP
11    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(V_STR, 200, (I-1)*200 + 1));
12   END LOOP;
13 END;
14 /

CREATE TABLE "YANGTK"."T"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREA
SE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "YANGTK"

PL/SQL 过程已成功完成。
后面这个方法虽然麻烦一些，但是功能很强，可以设置过滤、转换等功能，这在随后的文章中会进一步描述。

如果要获取XML或DDL，那么可以直接使用DBMS_METADATA过程的GET_DDL和GET_XML函数。这种方法即简单又方便。
不过DBMS_METADATA包的功能很强大，使用GET_DDL和GET_XML只能返回默认的情况，采用其他的方法可以设置过滤的条件，可以设置转换，而这些功能必须通过OPEN方式打开，然后通过ADD_TRANSFORM之类的过程进行转换。

看一个最简单的例子，有的时候我们只希望获取表结构而对表的物理存储结构不感兴趣，那么可以通过下面的过程实现：

SQL> DECLARE
2   V_STR CLOB;
3   V_HANDLE NUMBER;
4   V_TRANS_HANDLE NUMBER;
5 BEGIN
6   V_HANDLE := DBMS_METADATA.OPEN('TABLE');
7   DBMS_METADATA.SET_FILTER(V_HANDLE, 'NAME', 'T', 'TABLE');
8   V_TRANS_HANDLE := DBMS_METADATA.ADD_TRANSFORM(V_HANDLE, 'DDL');
9   DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRANS_HANDLE, 'SEGMENT_ATTRIBUTES', FALSE);
10   V_STR := DBMS_METADATA.FETCH_CLOB(V_HANDLE);
11   FOR I IN 1..CEIL(DBMS_LOB.GETLENGTH(V_STR)/200) LOOP
12    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(V_STR, 200, (I-1)*200 + 1));
13   END LOOP;
14 END;
15 /

CREATE TABLE "YANGTK"."T"
( "ID" NUMBER
)

PL/SQL 过程已成功完成。

下面通过设置REMAP参数，将SCHEMA转换为TEST，将表空间转换为USERS，这需要用到MODIFY方式：

SQL> DECLARE
2   V_STR CLOB;
3   V_HANDLE NUMBER;
4   V_TRANS_HANDLE NUMBER;
5 BEGIN
6   V_HANDLE := DBMS_METADATA.OPEN('TABLE');
7   DBMS_METADATA.SET_FILTER(V_HANDLE, 'NAME', 'T', 'TABLE');
8   V_TRANS_HANDLE := DBMS_METADATA.ADD_TRANSFORM(V_HANDLE, 'MODIFY');
9   DBMS_METADATA.SET_REMAP_PARAM(V_TRANS_HANDLE, 'REMAP_SCHEMA', 'YANGTK', 'TEST');
10   DBMS_METADATA.SET_REMAP_PARAM(V_TRANS_HANDLE, 'REMAP_TABLESPACE', 'YANGTK', 'USERS');
11   V_TRANS_HANDLE := DBMS_METADATA.ADD_TRANSFORM(V_HANDLE, 'DDL');
12   DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRANS_HANDLE, 'SEGMENT_ATTRIBUTES', TRUE);
13   DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRANS_HANDLE, 'STORAGE', FALSE);
14   V_STR := DBMS_METADATA.FETCH_CLOB(V_HANDLE);
15   FOR I IN 1..CEIL(DBMS_LOB.GETLENGTH(V_STR)/200) LOOP
16    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(V_STR, 200, (I-1)*200 + 1));
17   END LOOP;
18 END;
19 /

CREATE TABLE "TEST"."T"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE "USERS"

PL/SQL 过程已成功完成。

合理的使用DBMS_METADATA包，可以定制、修改数据库中的源数据，获取希望得到的结果，基本上EXPDP和IMPDP的一些转换功能都可以通过调用DBMS_METADATA包来实现。