有关Dump 文件的命令有exp/imp 和 expdp/impdp。 这四个命令之前都有整理过相关的文章。
ORACLE EXP/IMP 说明
http://blog.csdn.net/tianlesoftware/article/details/4718366
exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项
http://blog.csdn.net/tianlesoftware/article/details/6093973
Oracle expdp/impdp 使用示例
http://blog.csdn.net/tianlesoftware/article/details/6260138
Oracle 10g Data Pump Expdp/Impdp 详解
http://blog.csdn.net/tianlesoftware/article/details/4674224
Oracle expdp/impdp 从高版本 到 低版本 示例
http://blog.csdn.net/tianlesoftware/article/details/6533421
对于Dump 文件,我们不能直接提取出Data数据,但是我们可以通过相关的参数,从Dump文件中提取出对应的DDL 语句。
(1)如果是导出导入(exp/imp),那么是indexfile参数。
(2)如果是数据泵(expdp/impdp),那么是sqlfile 参数。
准备工作:
SYS@anqing1(rac1)> create user dvdidentified by dvd;
User created.
SYS@anqing1(rac1)> grant dba to dvd;
Grant succeeded.
SYS@anqing1(rac1)> conn dvd/dvd;
Connected.
DVD@anqing1(rac1)> create table t1(idnumber);
Table created.
DVD@anqing1(rac1)> insert into t1values(1);
1 row created.
DVD@anqing1(rac1)> commit;
Commit complete.
DVD@anqing1(rac1)> create index idx_t1on t1(id);
Index created.
DVD@anqing1(rac1)>
一.使用导出导入命令
1.1 导出dvd 用户的数据,生成dump文件
[oracle@rac1 ~]$ exp dvd/dvd file=dvd.dmpowner=dvd
Export: Release 10.2.0.4.0 - Production onWed Sep 21 19:50:14 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set andAL16UTF16 NCHAR character set
server uses ZHS16GBK character set(possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objectsand actions
. exporting foreign function library namesfor user DVD
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions foruser DVD
About to export DVD's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DVD's tables via ConventionalPath ...
. . exporting table T1 1 rows exported
EXP-00091: Exporting questionablestatistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrityconstraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional andextensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objectsand actions
. exporting statistics
Export terminated successfully withwarnings.
1.2 从dump 文件里提取DDL语句
[oracle@rac1 ~]$ imp dvd/dvd file=dvd.dmpfromuser=dvd touser=dvd indexfile=dvd.sql
Import: Release 10.2.0.4.0 - Production onWed Sep 21 19:50:50 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 viaconventional path
import done in US7ASCII character set andAL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possiblecharset conversion)
. . skipping table "T1"
Import terminated successfully withoutwarnings.
这里要注意2点:
(1) 该import 命令并没有真正的import data,而只是生成了我们对应用户下所有DDL的sql 语句。
(2) 对于表的DDL语句,用REM 进行了注释。
[oracle@rac1 ~]$ cat dvd.sql
REMCREATE TABLE "DVD"."T1" ("ID" NUMBER)PCTFREE 10 PCTUSED 40 INITRANS
REM1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
REMBUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM... 1 rows
CONNECT DVD;
CREATE INDEX "DVD"."IDX_T1"ON "T1" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS"LOGGING ;
如果只想看索引的DDL,那么可以用grep命令,讲REM 的不显示。
Linux Grep 命令说明
http://blog.csdn.net/tianlesoftware/article/details/6277193
[oracle@rac1 ~]$ cat dvd.sql|grep -v REM
CONNECT DVD;
CREATE INDEX"DVD"."IDX_T1" ON "T1" ("ID" ) PCTFREE10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS"LOGGING ;
二.数据泵(expdp/impdp)
2.1 导出dvd用户的数据
[oracle@rac1 ~]$ expdp dvd/dvddirectory=backup dumpfile=dvd.dmp schemas=dvd
Export: Release 10.2.0.4.0 - Production onWednesday, 21 September, 2011 20:16:59
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preservedatabase integrity.
Starting "DVD"."SYS_EXPORT_SCHEMA_01": dvd/******** directory=backupdumpfile=dvd.dmp schemas=dvd
Estimate in progress using BLOCKS method...
Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object typeSCHEMA_EXPORT/SYSTEM_GRANT
Processing object typeSCHEMA_EXPORT/ROLE_GRANT
Processing object typeSCHEMA_EXPORT/DEFAULT_ROLE
Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported"DVD"."T1" 4.906 KB 1 rows
Master table"DVD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DVD.SYS_EXPORT_SCHEMA_01is:
/u01/backup/dvd.dmp
Job"DVD"."SYS_EXPORT_SCHEMA_01" successfully completed at20:17:34
2.2 产生DDL
[oracle@rac1 ~]$ impdp dvd/dvddirectory=backup dumpfile=dvd.dmp sqlfile=dvd.sql
Import: Release 10.2.0.4.0 - Production onWednesday, 21 September, 2011 20:18:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real ApplicationClusters, OLAP, Data Mining
and Real Application Testing options
Master table"DVD"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting"DVD"."SYS_SQL_FILE_FULL_01": dvd/******** directory=backupdumpfile=dvd.dmp sqlfile=dvd.sql
Processing object type SCHEMA_EXPORT/USER
Processing object typeSCHEMA_EXPORT/SYSTEM_GRANT
Processing object typeSCHEMA_EXPORT/ROLE_GRANT
Processing object typeSCHEMA_EXPORT/DEFAULT_ROLE
Processing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object typeSCHEMA_EXPORT/TABLE/TABLE
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job"DVD"."SYS_SQL_FILE_FULL_01" successfully completed at20:18:54
2.3 查看DDL 文本
[oracle@rac1 backup]$ cat dvd.sql
-- CONNECT DVD
-- new object type path is:SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "DVD" IDENTIFIED BYVALUES '1111602792579CCE'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path is:SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO"DVD";
-- new object type path is:SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "DVD";
-- new object type path is:SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "DVD" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT DVD
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),export_db_name=>'ANQING.REGRESS.RDBMS.DEV.US.ORACLE.COM',inst_scn=>'9530068');
COMMIT;
END;
/
-- new object type path is:SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "DVD"."T1"
( "ID" NUMBER
)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)
TABLESPACE "USERS" ;
-- new object type path is:SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX"DVD"."IDX_T1" ON "DVD"."T1"("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "DVD"."IDX_T1" NOPARALLEL;
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE IND_NAME VARCHAR2(60);
IND_OWNER VARCHAR2(60);
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
IND_NAME := 'IDX_T1'; IND_OWNER:= 'DVD';
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)
VALUES ('I', 4, 0, IND_NAME, NULL, NULL, 'DVD', 1, 1, 1, 1, 1, 1, 0, 1,NULL, NULL, NULL, NULL, TO_DATE('2011-09-21 19:45:20','YYYY-MM-DD:HH24:MI:SS'));
DBMS_STATS.IMPORT_INDEX_STATS( '"' || ind_owner || '"','"' || ind_name || '"', NULL, '"IMPDP_STATS"', NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
从exp/imp 与 expdp/impdp 的DDL 结果进行对比,expdp/impdp 提取DDL 语句的更详细,可读性要好很多。
-------------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Weibo: http://weibo.com/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群:62697716(满); DBA2 群:62697977(满)DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群:83829929(满) DBA5群: 142216823(满)
DBA6 群:158654907(满) DBA7 群:69087192(满)DBA8 群:172855474
DBA 超级群2:151508914 DBA9群:102954821 聊天 群:40132017(满)
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
分享到:
相关推荐
Oracle常用dump命令介绍
批处理导出oracle数据库为dump文件,之后可以正常导入
Dump文件编辑中文版 分析DUMP文件数据 修改数据
C++ windows工程中 简单生成 dump文件,以便问题定位
dump文件查看器使用方法,分析蓝屏原因
修改oracle的dmp文件版本,可以把高版本导出的dmp文件修改为低版本,从而导入到低版本的oracle数据库.
3.在这边点击 Executables,在Export Dump:和Import Dump:中分别输入Oracle安装路径\product\10.1.0\db_3\BIN\expdp.exe,Oracle安装路径\oracle\product\10.1.0\db_3\BIN\impdp.exe,点 OK 4.进入 Database|Export|...
用于分析dump文件,很好用的一款内存分析工具,可以用来分析OOM等问题
使用说明:dump文件只能在debug模式下使用。运行有问题的程序,程序产生dump文件后,双击dump文件 ,文件将在vs中打开,双击vs中右边按钮“Debug with Mixed”,dump文件将自动定位到抛出异常的那句代码。具体看代码...
利用C++生成Dump文件,以后程序崩溃了,可以找到哪个函数出的问题。非常好用,企业级的应用!
DumpTool主要用来在用户机器上对目标进程生成dump文件,定位“卡死”、Crash等问题。 (1)MiniDump: 表示生成一个包含必要信息的dump文件,文件大小约200-500k,具体Flag =MiniDumpNormal|...
c++ 生成dump文件小程序。dump文件为调试程序的一种文件。
IBM java dump 文件分析工具,分析java堆栈信息
qt vs编译器下生成dump文件,方便调试。
为处理windows程序崩溃问题,有必要引入异常捕获模块。本资源主要讲述如何配置产生dump文件的环境以及dump文件产生后的分析定位。
java堆栈信息dump文件
水卡 文件 明码 dump文件 研究
附件是Dump文件的生成方式和作用,Dump文件对解决程序崩溃有着非常便捷的作用,非常不错的工作机制。
dump文件分析工具 memoryanalyzer_v1.5.0.rar