今天开发部的同事，让我帮他将生产库的某个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博客

生成创建表的ddl语句

最新文章