一、获取帮助
exp –help 或 exp help=y，效果一样。
[oracle@ocpserver ~]$ exp help=y Export: Release 10.2.0.1.0 - Production on Sat Jun 22 10:50:55 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. You can let Export prompt you for parameters by entering the EXP command followed by your username/password: Example: EXP SCOTT/TIGER Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords: Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) DIRECT direct path (N) TRIGGERS export triggers (Y) LOG log file of screen output STATISTICS analyze objects (ESTIMATE) ROWS export data rows (Y) PARFILE parameter filename CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y) OBJECT_CONSISTENT transaction set to read only during object export (N) FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file FLASHBACK_SCN SCN used to set session snapshot back to FLASHBACK_TIME time used to get the SCN closest to the specified time QUERY select clause used to export a subset of a table RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE TTS_FULL_CHECK perform full or partial dependency check for TTS VOLSIZE number of bytes to write to each tape volume TABLESPACES list of tablespaces to export TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TEMPLATE template name which invokes iAS mode export Export terminated successfully without warnings. [oracle@ocpserver ~]$
二、实验exp/imp
1、建立测试表jack
create table jack as select * from dba_objects;
insert into jack select * from jack;
说明：大约352万数据，为说明和expdp的对比做参考，可以更多。
2、导入导出指令准备
导出：exp jack/oracle file='/opt/oracle/exp_imp/jack01.dmp' log='/opt/oracle/exp_imp/jack01.log' rows=y tables=jack
导入：imp jack/oracle file='/opt/oracle/exp_imp/jack01.dmp' log='/opt/oracle/exp_imp/jack_imp01.log' full=y ignore=y
相关指令说明可以用exp help=y/imp help=y来查看各参数含义。
3、导入示例
[oracle@ocpserver exp_imp]$ exp jack/oracle file='/opt/oracle/exp_imp/jack01.dmp' log='/opt/oracle/exp_imp/jack01.log' rows=y tables=jack Export: Release 10.2.0.1.0 - Production on Sat Jun 22 11:08:12 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table JACK 3522400 rows exported Export terminated successfully without warnings. [oracle@ocpserver exp_imp]$ date Sat Jun 22 11:13:23 CST 2013
说明：
1）导入大约5分钟左右。
2）导出过程中导出日志/opt/oracle/exp_imp/jack01.log基本不写，等导出完成后这个日志会记录导出的一些信息，和exp导出打印在屏幕上的信息一致
[oracle@ocpserver exp_imp]$ more jack01.log Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table JACK 3522400 rows exported Export terminated successfully without warnings. [oracle@ocpserver exp_imp]$
4、导入示例
1）把存在的jack表修改为jackbak表
SQL> alter table jack rename to jackbak;
Table altered.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JACKBAK TABLE
T1 TABLE
SQL>
2）导入示例
[oracle@ocpserver exp_imp]$ imp jack/oracle file='/opt/oracle/exp_imp/jack01.dmp' log='/opt/oracle/exp_imp/jack_imp01.log' full=y ignore=y Import: Release 10.2.0.1.0 - Production on Sat Jun 22 11:19:01 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8ISO8859P1 character set (possible charset conversion) . importing JACK's objects into JACK . importing JACK's objects into JACK . . importing table "JACK" 3522400 rows imported Import terminated successfully without warnings. [oracle@ocpserver exp_imp]$ date Sat Jun 22 11:25:46 CST 2013
说明：
1)在导入过程中，大约经历了6分钟，比导出稍微慢一些
2)在导入过程中，可以看到产生了jack表，但是jack表中数据数量始终是0；导入完成后，可以看到表的数据了。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JACKBAK TABLE
T1 TABLE
JACK TABLE
SQL> select count(*) from jack;
COUNT(*)
----------
0
SQL>
3)导出完成检查jack表数据为导入数据
SQL> select count(*) from jack;
COUNT(*)
----------
3522400
SQL>
三、实验expdp/impdp
1、获取帮助
和exp/imp方法类似，expdp help=y/impdp help=y，不再列出。
2、expdp/impdp准备
1)存在目录/opt/oracle/exp_imp，如不存在，自己创建。
2)创建注册
(1）创建
SQL> CREATE DIRECTORY DPDATA AS '/opt/oracle/exp_imp';
Directory created.
(2）查询注册
SQL> SET LINE 300 PAGES 30000 SQL> COL OWNER FOR A10 SQL> COL DIRECTORY_NAME FOR A15 SQL> COL DIRECTORY_PATH FOR A70 SQL> SELECT * FROM DBA_DIRECTORIES; OWNER DIRECTORY_NAME DIRECTORY_PATH ---------- --------------- ---------------------------------------------------------------------- SYS ADMIN_DIR /ade/aime_10.2_lnx_push/oracle/md/admin SYS SUBDIR /opt/oracle/product/demo/schema/order_entry//2002/Sep SYS DATA_FILE_DIR /opt/oracle/product/demo/schema/sales_history/ SYS WORK_DIR /ade/aime_10.2_lnx_push/oracle/work SYS LOG_FILE_DIR /opt/oracle/product/demo/schema/log/ SYS MEDIA_DIR /opt/oracle/product/demo/schema/product_media/ SYS XMLDIR /opt/oracle/product/demo/schema/order_entry/ SYS DATA_PUMP_DIR /opt/oracle/product/rdbms/log/ SYS DPDATA /opt/oracle/exp_imp 9 rows selected. SQL>
3)授权读写
SQL> GRANT READ,WRITE ON DIRECTORY DPDATA TO JACK; GRANT READ,WRITE ON DIRECTORY DPDATA TO JACK * ERROR at line 1: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself SQL> conn / as sysdba Connected. SQL> GRANT READ,WRITE ON DIRECTORY DPDATA TO JACK; Grant succeeded. SQL> commit; Commit complete. SQL> conn jack/oracle; Connected. SQL> show user; USER is "JACK" SQL>
3、expdp/impdp指令准备
expdp jack/oracle directory=dpdata dumpfile=jack_expdp_01.dmp LOGFILE=jack_expdp_01.log tables=jack;
expdp jack/oracle directory=dpdata PARALLEL=3 dumpfile=jack_expdp_02.dmp LOGFILE=jack_expdp_02.log tables=jack;
impdp jack/oracle directory=dpdata dumpfile=jack_expdp_01.dmp logfile=jack_expdp_01.log;
impdp jack/oracle directory=dpdata PARALLEL=3 dumpfile=jack_expdp_01.dmp logfile=jack_expdp_01.log;
4、expdp非并行模式导出示例
[oracle@ocpserver exp_imp]$ expdp jack/oracle directory=dpdata dumpfile=jack_expdp_01.dmp LOGFILE=jack_expdp_01.log tables=jack; Export: Release 10.2.0.1.0 - Production on Saturday, 22 June, 2013 11:44:44 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "JACK"."SYS_EXPORT_TABLE_01": jack/******** directory=dpdata dumpfile=jack_expdp_01.dmp LOGFILE=jack_expdp_01.log tables=jack Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 384 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "JACK"."JACK" 328.0 MB 3522400 rows Master table "JACK"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for JACK.SYS_EXPORT_TABLE_01 is: /opt/oracle/exp_imp/jack_expdp_01.dmp Job "JACK"."SYS_EXPORT_TABLE_01" successfully completed at 11:45:44
说明：
1）比expdp导出时间少1分钟左右
2）很明显，多了开始和结束时间，更加方便查看导入导出效率；
3）加入了导出行数（3522400），导出数据大小（328M），更显方便
4）expdp导出日志即使写入log日志。而exp导出日志是导出完成（不论成功失败）后才写入到log文件中。
5）expdp导出数据比exp导出数据小一些，exp导出同样表3522400行大小为366M，而exp为328M,如下
-rw-r--r-- 1 oracle oinstall 366M Jun 22 11:12 jack01.dmp
-rw-r--r-- 1 oracle oinstall 464 Jun 22 11:12 jack01.log
-rw-r----- 1 oracle oinstall 329M Jun 22 11:45 jack_expdp_01.dmp
-rw-r--r-- 1 oracle oinstall 991 Jun 22 11:45 jack_expdp_01.log
-rw-r--r-- 1 oracle oinstall 548 Jun 22 11:24 jack_imp01.log
5、并行导出
[oracle@ocpserver exp_imp]$ expdp jack/oracle directory=dpdata PARALLEL=3 job_name=jack_expdp_03 dumpfile=jack_expdp_03.dmp LOGFILE=jack_expdp_03.log tables=jack; Export: Release 10.2.0.1.0 - Production on Saturday, 22 June, 2013 11:54:15 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "JACK"."JACK_EXPDP_03": jack/******** directory=dpdata PARALLEL=3 job_name=jack_expdp_03 dumpfile=jack_expdp_03.dmp LOGFILE=jack_expdp_03.log tables=jack Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 384 MB lProcessing object type TABLE_EXPORT/TABLE/TABLE . . exported "JACK"."JACK" 328.0 MB 3522400 rows Master table "JACK"."JACK_EXPDP_03" successfully loaded/unloaded ****************************************************************************** Dump file set for JACK.JACK_EXPDP_03 is: /opt/oracle/exp_imp/jack_expdp_03.dmp Job "JACK"."JACK_EXPDP_03" successfully completed at 11:55:27
说明
1）导出时间貌似快了那么一点，如果大数据导出，效果会更明显。
2）在11gr2版本中层导出30g的数据库，并行数字为3，dumpfile文件为3，大约20分钟。
3）分文件，及如果10多G的文件导出为一个文件，则读写对操作系统性能要求较高，所以把文件分成几个3-4G的文件，这样速度应该快点。
expdp jack/oracle directory=dpdata PARALLEL=3 job_name=jack_expdp_03 dumpfile=jack_expdp_%u.dmp LOGFILE=jack_expdp_03.log tables=jack;
6、导入
[oracle@ocpserver exp_imp]$ impdp jack/oracle directory=dpdata dumpfile=jack_expdp_01.dmp logfile=jack_expdp_01.log; Import: Release 10.2.0.1.0 - Production on Saturday, 22 June, 2013 12:29:42 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Master table "JACK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "JACK"."SYS_IMPORT_FULL_01": jack/******** directory=dpdata dumpfile=jack_expdp_01.dmp logfile=jack_expdp_01.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "JACK"."JACK" 328.0 MB 3522400 rows Job "JACK"."SYS_IMPORT_FULL_01" successfully completed at 12:32:25 [oracle@ocpserver exp_imp]$
说明：
1）导入过程中多了个表临时表：“SYS_IMPORT_FULL_01”
可以通过这个临时表查看导入进度等。表结构为：
SQL> desc SYS_IMPORT_FULL_01 Name Null? Type ----------------------------------------- -------- ---------------------------- PROCESS_ORDER NUMBER DUPLICATE NUMBER DUMP_FILEID NUMBER DUMP_POSITION NUMBER DUMP_LENGTH NUMBER DUMP_ALLOCATION NUMBER COMPLETED_ROWS NUMBER ERROR_COUNT NUMBER ELAPSED_TIME NUMBER OBJECT_TYPE_PATH VARCHAR2(200) OBJECT_PATH_SEQNO NUMBER OBJECT_TYPE VARCHAR2(30) IN_PROGRESS CHAR(1) OBJECT_NAME VARCHAR2(500) OBJECT_LONG_NAME VARCHAR2(4000) OBJECT_SCHEMA VARCHAR2(30) ORIGINAL_OBJECT_SCHEMA VARCHAR2(30) PARTITION_NAME VARCHAR2(30) SUBPARTITION_NAME VARCHAR2(30) FLAGS NUMBER PROPERTY NUMBER COMPLETION_TIME DATE OBJECT_TABLESPACE VARCHAR2(30) SIZE_ESTIMATE NUMBER OBJECT_ROW NUMBER PROCESSING_STATE CHAR(1) PROCESSING_STATUS CHAR(1) BASE_PROCESS_ORDER NUMBER BASE_OBJECT_TYPE VARCHAR2(30) BASE_OBJECT_NAME VARCHAR2(30) BASE_OBJECT_SCHEMA VARCHAR2(30) ANCESTOR_PROCESS_ORDER NUMBER DOMAIN_PROCESS_ORDER NUMBER PARALLELIZATION NUMBER UNLOAD_METHOD NUMBER GRANULES NUMBER SCN NUMBER GRANTOR VARCHAR2(30) XML_CLOB CLOB NAME VARCHAR2(30) VALUE_T VARCHAR2(4000) VALUE_N NUMBER IS_DEFAULT NUMBER FILE_TYPE NUMBER USER_DIRECTORY VARCHAR2(4000) USER_FILE_NAME VARCHAR2(4000) FILE_NAME VARCHAR2(4000) EXTEND_SIZE NUMBER FILE_MAX_SIZE NUMBER PROCESS_NAME VARCHAR2(30) LAST_UPDATE DATE WORK_ITEM VARCHAR2(30) OBJECT_NUMBER NUMBER COMPLETED_BYTES NUMBER TOTAL_BYTES NUMBER METADATA_IO NUMBER DATA_IO NUMBER CUMULATIVE_TIME NUMBER PACKET_NUMBER NUMBER OLD_VALUE VARCHAR2(4000) SEED NUMBER LAST_FILE NUMBER USER_NAME VARCHAR2(30) OPERATION VARCHAR2(30) JOB_MODE VARCHAR2(30) CONTROL_QUEUE VARCHAR2(30) STATUS_QUEUE VARCHAR2(30) REMOTE_LINK VARCHAR2(4000) VERSION NUMBER DB_VERSION VARCHAR2(30) TIMEZONE VARCHAR2(64) STATE VARCHAR2(30) PHASE NUMBER GUID RAW(16) START_TIME DATE BLOCK_SIZE NUMBER METADATA_BUFFER_SIZE NUMBER DATA_BUFFER_SIZE NUMBER DEGREE NUMBER PLATFORM VARCHAR2(101) ABORT_STEP NUMBER INSTANCE VARCHAR2(60) SQL>
这个临时表数据为一直是276行：
SQL> select count(*) from SYS_IMPORT_FULL_01; COUNT(*) ---------- 276 SQL>
2）导入时间和imp快了一些，如果数据量大的话，这个时间应该更加明显；
7，并行导入
[oracle@ocpserver exp_imp]$ impdp jack/oracle directory=dpdata PARALLEL=3 dumpfile=jack_expdp_01.dmp logfile=jack_expdp_01.log; Import: Release 10.2.0.1.0 - Production on Saturday, 22 June, 2013 12:35:44 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Master table "JACK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "JACK"."SYS_IMPORT_FULL_01": jack/******** directory=dpdata PARALLEL=3 dumpfile=jack_expdp_01.dmp logfile=jack_expdp_01.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA select count(*) from SYS_IMPORT_FULL_01; . . imported "JACK"."JACK" 328.0 MB 3522400 rows Job "JACK"."SYS_IMPORT_FULL_01" successfully completed at 12:38:37 [oracle@ocpserver exp_imp]$
本章小结：
1）exp/imp是早起版本中的导入导出工具，简单方便；
2）expdp/impdp功能在早起版本中做过诸多改进，在后续继续学习其他特性。
3）导入导出备份有许多优点，但比起RMAN来，还是小巫见大巫了。