oracle建库脚本导出
使用expdp/impdp用impdp从expdp的导出文件生成sql脚本很简单,并且提供诸多选项可以自动转换表名称、SCHEMA、表名等,相当方便。比如:
- impdp / directory=dmp_output dumpfile=db.dmp sqlfile=db.sql remap_schema=scott:scott1 remap_tablespace=users:users1
这样就生成了一个建库的脚本,并且其中所有scott自动转换成scott1,表空间users自动转换成users1。但这个生成的脚本一般来说不是我们想要的结果,impdp/expdp主要是用来导入数据的,生成脚本只是一个附加的功能,并且生成的脚本实际上就是导入数据时执行的脚本(数据本身除外),所以其中包括了很多我们不需要的信息,典型的就是统计信息也会一起生成,例如生成的脚本里面会包括这样的sql:
- DECLARE I_N VARCHAR2(60);
- I_O VARCHAR2(60);
- c DBMS_METADATA.T_VAR_COLL;
- df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
- BEGIN
- DELETE FROM "SYS"."IMPDP_STATS";
- i_n := 'PK_EMPLOYEE';
- i_o := 'OA';
- INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES ('I',5,2,I_N,NULL,NULL,I_O,3071715,19335,3071715,1,1,251060,2,614343,NULL,NULL,NULL,NULL,TO_DATE('2014-03-15 02:17:44',df),NULL);
- DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
- DELETE FROM "SYS"."IMPDP_STATS";
- END;
- /
作用是将源库上PK_EMPLOYEE这个主键的统计信息也一起导入到目标库中。观察impdp的输出:
- Processing object type DATABASE_EXPORT/SCHEMA/USER
- Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
- Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
- Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
- Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
- Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
- Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
- Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
- Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
- Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
- Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
- Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY
这个输出结果告诉我们impdp都导出了一些什么对象类型,完整的对象类型列表可以从dba_export_objects中查询到。对于上面提到的统计信息,对应的是DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS。所以,如果我们希望只导出我们想要的简洁sql,可以用两种方式告诉impdp,include或exclude选项(这两个选项是互斥的,也就是说不能同时使用)。比如我们希望只导出建表及索引语句,可以这样写:
- impdp / directory=dmp_output dumpfile=db.dmp sqlfile=db.sql remap_schema=scott:scott1 remap_tablespace=users:users1 include=DATABASE_EXPORT/SCHEMA/TABLE/TABLE,DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
如果我们希望在结果中排除所有统计信息,那就这样:
- impdp / directory=dmp_output dumpfile=db.dmp sqlfile=db.sql remap_schema=scott:scott1 remap_tablespace=users:users1 exclude=DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS,DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
等等。这样就能简单高效的得到我们需要的建库脚本了。唯一不足的是,里面还是会包括了存储相关的信息,比如建表的STORAGE子句,另外生成的脚本文件头几行ALTER SESSION SET EVENTS...语句对我们建库没什么用处,可以手工删除,其中有几个事件号比如25475,除了知道“Reserved for Rules Engine”,根本不清楚具体什么作用,还是删掉来得保险一点。