exp和expdp几种常见的使用场景操作介绍

bisal的个人杂货铺 2021-09-06

750

同事前几天问到exp/expdp根据条件导出一张/多张表数据的问题，借此机会，依次对这些需求做个实验，仅供参考。

场景1 exp根据条件导出一张表的数据

检索条件使用query参数来定义，如果采用命令行执行，对特殊字符，例如<等，需要转义，

[oracle@bisal ~]$ exp bisal/bisal file=/home/oracle/exp_1_table.dmp tables=test01 query=\"where id \>= 500 and id \< 600\"
...
About to export specified tables via Conventional Path ...
. . exporting table                         TEST01        100 rows exported
Export terminated successfully without warnings.

dmp是二进制的文件，可以使用strings查看，但是从dmp文件其实显示的是表的创建语句及其索引，约束，统计信息的统计信息等，因此如果使用了query参数最好有记录，不然难以找到where子句到底写了什么，

[oracle@bisal ~]$ strings exp_1_table.dmp
EXPORT:V11.02.00
DBISAL
RTABLES
8192
                                       Sat Sep 4 15:30:22 2021/home/oracle/exp_1_table.dmp
#G#G
#G#G
+00:00
BYTE
UNUSED
INTERPRETED
DISABLE:ALL
METRICST
TABLE "TEST01"
CREATE TABLE "TEST01" ("ID" NUMBER, "RANDOM_STRING" VARCHAR2(4000))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
INSERT INTO "TEST01" ("ID", "RANDOM_STRING") VALUES (:1, :2)
NFT9T4VK6FETVJCWT6PXNTV0NBLMAY
...
METRICSTreferential integrity constraints
METRICET 100
METRICSTtriggers
METRICET 100
METRICSTbitmap, functional and extensible indexes
METRICET 100
METRICSTposttables actions
METRICET 100
METRICSTPost-inst procedural actions
METRICET 100
METRICETG0
EXIT
EXIT

场景2 exp使用配置文件导出一张表的数据

使用参数文件就是一个很好的记录方法，而且有个好处，就是特殊字符，例如<，不需要转义，如下所示，

[oracle@bisal ~]$ vi exp.txt
userid=bisal/bisal
file=/home/oracle/exp_part.dmp
log=/home/oracle/exp_part.log
tables=test01
buffer=1024
query="where id >= 500 and id < 600"

执行指令，只需要增加parfile参数，

[oracle@bisal ~]$ exp parfile=exp.txt
...
About to export specified tables via Conventional Path ...
. . exporting table                         TEST01        100 rows exported
Export terminated successfully without warnings.

场景3 exp根据条件导出多张表的数据

如果要根据条件导出多张表，可以加在tables中，但是括号这种特殊字符，需要转义，同时这个条件query，要求必须适合所有的表，

[oracle@bisal ~]$ exp bisal/bisal file=/home/oracle/exp_2_table.dmp tables=\(test01,test02\) query=\"where id \>= 500 and id \< 600\"
...
About to export specified tables via Conventional Path ...
. . exporting table                         TEST01        100 rows exported
. . exporting table                         TEST02        100 rows exported
Export terminated successfully without warnings.

例如增加表test01一个新的字段test_01_id，

SQL> alter table test01 add test_01_id int;
Table altered.

SQL> update test01 set test_01_id=id;
1000 rows updated.

因为表test02没有test_01_id字段，所以提示错误，

[oracle@bisal ~]$ exp bisal/bisal file=/home/oracle/exp_2_condition_table.dmp tables=\(test01,test02\) query=\"where test_01_id \>= 500 and test_01_id \< 600\"
About to export specified tables via Conventional Path ...
. . exporting table                         TEST01        100 rows exported
. . exporting table                         TEST02
EXP-00056: ORACLE error 904 encountered
ORA-00904: "TEST_01_ID": invalid identifier
Export terminated successfully with warnings.

场景4 expdp根据条件导出一张表的数据

数据泵导出需要创建Oracle目录对象，

SQL> create directory bisal_exp_dir as '/home/oracle';
Directory created.

执行导出指令，可以看到，他和普通的exp区别就是他会将导出操作作为一个job在Oracle中执行，即使我在命令行，执行了ctrl+c，这个job不会因为前台进程的中断而停止，还会继续执行，除非在expdp的命令行执行中断操作，

[oracle@bisal ~]$ expdp bisal/bisal directory=bisal_exp_dir dumpfile=bisal_expdp.dmp tables=test01 query=\"where id \>= 500 and id \< 600\"
...
Starting "BISAL"."SYS_EXPORT_TABLE_01":  bisal/******** directory=bisal_exp_dir dumpfile=bisal_expdp.dmp tables=test01 query="where id >= 500 and id < 600"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BISAL"."TEST01"                            10.03 KB     100 rows
Master table "BISAL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BISAL.SYS_EXPORT_TABLE_01 is:
  home/oracle/bisal_expdp.dmp
Job "BISAL"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 4 16:42:24 2021 elapsed 0 00:00:37

场景5 expdp根据条件导出多张表的数据

这个和exp的操作几乎是相同的，

[oracle@jf-vra-app1461 ~]$ expdp bisal/bisal directory=bisal_exp_dir dumpfile=bisal_2_expdp.dmp tables=\(test01,test02\) query=\"where id \>= 500 and id \< 600\"

Export: Release 11.2.0.4.0 - Production on Sat Sep 4 17:59:51 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BISAL"."SYS_EXPORT_TABLE_01":  bisal/******** directory=bisal_exp_dir dumpfile=bisal_2_expdp.dmp tables=(test01,test02) query="where id >= 500 and id < 600"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BISAL"."TEST01"                            10.03 KB     100 rows
. . exported "BISAL"."TEST02"                            9.218 KB     100 rows
Master table "BISAL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BISAL.SYS_EXPORT_TABLE_01 is:
  home/oracle/bisal_2_expdp.dmp
Job "BISAL"."SYS_EXPORT_TABLE_01" successfully completed at Sat Sep 4 18:00:01 2021 elapsed 0 00:00:10