oracle导出字段不全,【EXP/IMP】从exp工具生成的不完整备份文件中快速获取数据库表空间创建语句...

这是一个鲜为人知的技巧，分享在此，希望对大家有帮助。结论是这样的：使用exp工具的“full=y”参数生成的备份文件中，最先写入的是数据库表空间的定义信息，即使在导出的过程中遇到故障，这部分最先导出的数据依然可以被imp工具识别并抽取出其中的SQL语句。实际验证一下这个过程。1.使用exp工具生成备份文件注意这里使用exp工具的“full=y”选项对system用户进行备份。ora10g@secdb /db_backup$ exp system/oracle file=export_system.dmp full=yExport: Release 10.2.0.1.0 - Production on Thu Mar 31 22:18:30 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setAbout to export the entire database .... exporting tablespace definitions. exporting profiles. exporting user definitions. exporting roles. exporting resource costs. exporting rollback segment definitions. exporting database links. exporting sequence numbers. exporting directory aliases. exporting context namespaces. exporting foreign function library names. exporting PUBLIC type synonymsEXP-00008: ORACLE error 1013 encounteredORA-01013: user requested cancel of current operationEXP-00000: Export terminated unsuccessfully在刚刚启动备份的一瞬间，我们使用“Ctrl+C”快捷键终止导出任务，获得如上的提示信息。如果等待整个导出过程完成将非常耗时，也不是很有必要。2.确认生成的备份文件ora10g@secdb /db_backup$ ls -l export_system.dmp-rw-r--r-- 1 oracle oinstall 48K Mar 31 22:18 export_system.dmp这里显示出文件仅有48K大小，显然包含的信息很有限。3.使用imp工具读取备份文件中的SQL语句ora10g@secdb /db_backup$ imp system/oracle file=export_system.dmp show=y log=export_system.log full=yImport: Release 10.2.0.1.0 - Production on Thu Mar 31 22:20:28 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathimport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set. importing SYSTEM's objects into SYSTEM"CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE  '/oracle/ora10gR""2/oradata/ora10g/undotbs01.dbf' SIZE 293601280       AUTOEXTEND ON NEXT 524""2880  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL ""CREATE TABLESPACE "SYSAUX" BLOCKSIZE 8192 DATAFILE  '/oracle/ora10gR2/orada""ta/ora10g/sysaux01.dbf' SIZE 346030080       AUTOEXTEND ON NEXT 10485760  M""AXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGM""ENT SPACE MANAGEMENT AUTO""CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE  '/oracle/ora10g""R2/oradata/ora10g/temp01.dbf' SIZE 26214400       AUTOEXTEND ON NEXT 655360""  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  UNIFORM. SIZE 1048576""CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE  '/oracle/ora10gR2/oradat""a/ora10g/users01.dbf' SIZE 5242880       AUTOEXTEND ON NEXT 1310720  MAXSIZ""E 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT S""PACE MANAGEMENT AUTO""CREATE TABLESPACE "TBS_SEC_D" BLOCKSIZE 8192 DATAFILE  '/oracle/ora10gR2/or""adata/ora10g/tbs_sec_d_01.dbf' SIZE 10485760       AUTOEXTEND ON NEXT 8192 "" MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SE""GMENT SPACE MANAGEMENT AUTO"……省略其他输出信息IMP-00009: abnormal end of export fileImport terminated successfully with warnings.细心观察屏幕输出的信息(或查看最终生成的export_system.log