生成创建表的ddl语句

       今天开发部的同事，让我帮他将生产库的某个schema下的表的创建语句DDL导出来，要求导出SQL语句。
最开始想到的是用exp工具配上rows=n的参数，导出，然后再导入到测试库。后来同事强调，最好是导出创建
表的DDL语句。这让我想到了DBMS_METADATA.GET_DDL方式来获取SQL.

下面以scott schema为例进行演示说明
生成scott schema tables DDL 脚本

conn scott/tiger
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;        #生成创建index的ddl语句
spool off;

上面的脚本可以将创建table和index的DDL语句都导出来，但是有一个小问题，就是在每个DDL末尾没有结束的分号; 

为了提高脚本的普适性，对上面的脚本进行下改造

set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 200
define schema=&1
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select to_char(dbms_metadata.get_ddl('TABLE',table_name,owner))
from dba_tables       
where owner=upper('&1');

select to_char(dbms_metadata.get_ddl('INDEX',index_name,table_owner))        #生成创建index的ddl语句
from dba_indexes
where table_owner=upper('&1');

输出结果类似下面

      CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  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 DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  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 DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28697282/viewspace-1480739/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28697282/viewspace-1480739/