Dbabc.Net

通过 DBMS_METADATA 可以获得 表，索引或者procedure的ddl语句。

The kind of object to be retrieved. This can be either a particular object type (such as a table, index, or procedure) or a heterogeneous collection of object types that form a logical unit (such as a database export or schema export).
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.

官方说明：http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm

以下是实际操作实例：

1、获得一个表的ddl语句：

SQL＞ SET SERVEROUTPUT ON
SQL＞ SET LINESIZE 1000
SQL＞ SET FEEDBACK OFF
SQL＞ set long 999999
SQL＞ SET PAGESIZE 1000

SQL＞ SELECT DBMS_METADATA.GET_DDL('TABLE','DBABC') from dual;

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

CREATE TABLE "SYS"."DBABC"
(    "DUMMY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA
NS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXE
XTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUP
S 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

经过验证其中的表名需要大写才可以,如果是小写的话会报错如下:

SQL＞ SELECT DBMS_METADATA.GET_DDL('TABLE','dbabc') from dual;
ERROR:
ORA-31603: object "dbabc" of type TABLE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

—去除storage等多余参数
2、获得某个用户下的所有表，索引，存储过程的ddl：

conn scott/triger
set pagesize 0
set long 90000
set feedback off
set echo off
spool scott_schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
      FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
      FROM USER_INDEXES u;
spool off;

3、获得索引创建用户的ddl语句

SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;

4、获得所有表空间的ddl语句

SELECT DBMS_METADATA.GET_DDL('USER',U.username) * FROM DBA_USERS U