oracle建库脚本导出

时间：2024-03-09 13:07:37

使用expdp/impdp用impdp从expdp的导出文件生成sql脚本很简单，并且提供诸多选项可以自动转换表名称、SCHEMA、表名等，相当方便。比如：

 

[sql] view plain copy
print?在CODE上查看代码片派生到我的代码片
  1. 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：

 

[sql] view plain copy
print?在CODE上查看代码片派生到我的代码片
  1. DECLARE I_N VARCHAR2(60);   
  2.   I_O VARCHAR2(60);   
  3.   c DBMS_METADATA.T_VAR_COLL;   
  4.   df varchar2(21) := \'YYYY-MM-DD:HH24:MI:SS\';   
  5. BEGIN  
  6.   DELETE FROM "SYS"."IMPDP_STATS";   
  7.   i_n := \'PK_EMPLOYEE\';   
  8.   i_o := \'OA\';   
  9.   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);  
  10.   
  11.   DBMS_STATS.IMPORT_INDEX_STATS(\'"\' || i_o || \'"\',\'"\' || i_n || \'"\',NULL,\'"IMPDP_STATS"\',NULL,\'"SYS"\');   
  12.   DELETE FROM "SYS"."IMPDP_STATS";   
  13. END;   
  14. /  

 

作用是将源库上PK_EMPLOYEE这个主键的统计信息也一起导入到目标库中。观察impdp的输出：

 

[plain] view plain copy
print?在CODE上查看代码片派生到我的代码片
  1. Processing object type DATABASE_EXPORT/SCHEMA/USER  
  2. Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT  
  3. Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT  
  4. Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE  
  5. Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA  
  6. Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM  
  7. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC  
  8. Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA  
  9. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE  
  10. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT  
  11. Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION  
  12. Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION  
  13. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX  
  14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX  
  15. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT  
  16. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS  
  17. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS  
  18. Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW  
  19. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT  
  20. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS  
  21. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER  
  22. Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY  

 

这个输出结果告诉我们impdp都导出了一些什么对象类型，完整的对象类型列表可以从dba_export_objects中查询到。对于上面提到的统计信息，对应的是DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS。所以，如果我们希望只导出我们想要的简洁sql，可以用两种方式告诉impdp，include或exclude选项（这两个选项是互斥的，也就是说不能同时使用）。比如我们希望只导出建表及索引语句，可以这样写：

 

[sql] view plain copy
print?在CODE上查看代码片派生到我的代码片
  1. 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  

如果我们希望在结果中排除所有统计信息，那就这样：

 

[sql] view plain copy
print?在CODE上查看代码片派生到我的代码片
  1. 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”，根本不清楚具体什么作用，还是删掉来得保险一点。

相关文章