DBMS_METADATA获取DDL语句

在oracle 10g的版本中，可以使用DBMS_METADATA.GET_DDL包获取对象的DDL语句。

1.获取当前用户下指定表名的DDL语句

scott@DB11GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

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

  CREATE TABLE "SCOTT"."T"
   (    "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)
   ) 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 DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

2.获取当前用户下指定索引名的DDL语句

scott@DB11GR2> select dbms_metadata.get_ddl('INDEX','IDX_EMPNO') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_EMPNO')
--------------------------------------------------------------------------------

  CREATE INDEX "SCOTT"."IDX_EMPNO" ON "SCOTT"."T" ("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 DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

3.获取当前用户下指定视图名的DDL语句

scott@DB11GR2> L
  1* select dbms_metadata.get_ddl('VIEW','SALGT')
scott@DB11GR2> a  from dual;
  1* select dbms_metadata.get_ddl('VIEW','SALGT') from dual
scott@DB11GR2> /

DBMS_METADATA.GET_DDL('VIEW','SALGT')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SCOTT"."SALGT" ("EMPNO", "ENAME", "JOB", "MGR",
"HIREDATE", "SAL", "COMM", "DEPTNO") AS
  select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from t whe
re t.sal>2000

4.获取当前用户下指定过程的DDL语句

select dbms_metadata.get_ddl('PROCEDURE',procedure_name) from dual;

5.获取一个schema下所有的建表DDL语句

 SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

6.获取一个schema下所有的建索引DDL语句

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;

7.获取一个schema下所有的建视图DDL语句

SELECT DBMS_METADATA.GET_DDL('VIEW',u.VIEW_name) FROM USER_VIEWS u;

8.获取一个schema下所有的过程DDL语句

select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) from user_objects u where object_type = 'PROCEDURE';

9.获取一个schema下所有的函数DDL语句

select DBMS_METADATA.GET_DDL('FUNCTION',u.object_name) from user_objects u where object_type = 'FUNCTION';

10.也可以一个sql得到所有的表、索引、过程

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
  FROM USER_OBJECTS u
 where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

11.得到所有表空间的DDL语句

sys@DB11GR2> l
  1* SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS

12.得到用户的DDL语句

sys@DB11GR2> l
  1* SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U