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

清疚 2022-09-10 07:21 74阅读 0赞

同事前几天问到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

可以说expdp是exp的升级，具体可以参考这两个指令的help=y帮助信息，提供各种参数的说明，以及一些简单的例子。

之前写的和导入导出相关的文章，

《[导入导出的两个小错误][Link 1]》

《[解决导入过程中出现的ORA-02289错误][ORA-02289]》

《[Oracle导入导出的常见错误][Oracle]》

《[生产数据导入测试环境碰见的一些问题][Link 2]》

《[imp/exp导入导出的一些错误][imp_exp]》

《[使用exp导出报错EXP-00091][exp_EXP-00091]》

**近期更新的文章：**

《[优雅编写SQL的几个案例][SQL]》

《[优化前置思想的成本收益关系][Link 3]》

《[v$视图存储SQL的bug][v_SQL_bug]》

《[RPO和RTO是什么？][RPO_RTO]》

《[Linux的inode是什么？][Linux_inode]》

**文章分类和索引：**

[《公众号800篇文章分类和索引][800]》

[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81533dddf6da2b409a9e57b0a355e3bf0747b73415a994adbd0df4a294ef1fb2b58d2f5253&idx=1&mid=2247489230&scene=21&sn=4b2030c085a592f71de3527f43cc42dc#wechat_redirect
[ORA-02289]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814617ddf6cf01507c300719ad166bbd90cb1757324235af810564d7d37419302435150d59&idx=1&mid=2247484388&scene=21&sn=875654fc01255f44323443b4694e35ed#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814262ddf6cb740d253a17cabd54f9ff2780752df4af8587c92e430344650f7033c5ce6ad2&idx=1&mid=2247485329&scene=21&sn=7cf01bbfcc38a0477235d04587192755#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814636ddf6cf20de5bdaef143b0252f1b9623d601f7646f690ea3f2fa00aba8430ee5703c7&idx=1&mid=2247484357&scene=21&sn=d07328769ced2a7973788ebbdde76e01#wechat_redirect
[imp_exp]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814df4ddf6c4e2266e8b5a5945b10a66c8c88a399eb45f9503980be4c5e838c8fe6ac96fd6&idx=1&mid=2247485447&scene=21&sn=aec596757a180e6eb34f92a2cffceed1#wechat_redirect
[exp_EXP-00091]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814469ddf6cd7f112b9842fde70744e09fe69799ff8e2d15ada783aa29cbe70bfea682415b&idx=1&mid=2247483802&scene=21&sn=a76aa06b54c89b9c5ec3bb55bd3b6118#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a28addf52b9c0515fc590aca004c80579aa8491ec0d542b030701c0ec967bb2a787e7471&idx=1&mid=2247493497&scene=21&sn=7345dae2770665ddc3a64f2fb0242211#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a293ddf52b8577a6bc71c164d72c65026e7db9c71b289c9bd53b54b8ceef2e22dd1a7630&idx=1&mid=2247493472&scene=21&sn=d15854e84073618cf58e8fbe3c2c903d#wechat_redirect
[v_SQL_bug]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a2a9ddf52bbf0bde73afdb1df9e94e9c760c76b358c7e0394992f7edaa7b8002ba4cf404&idx=1&mid=2247493466&scene=21&sn=30923018edef405f179a0ec5f7bccdc0#wechat_redirect
[RPO_RTO]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a31fddf52a0912754eff08e7efad78fde1f7516c7803d87ba1d373bbcfd5fe723e5c8afb&idx=1&mid=2247493356&scene=21&sn=7c4e279f3eeee3b7803a87e1b07d0f52#wechat_redirect
[Linux_inode]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a354ddf52a42e4edaf05e640f8ce190f8196e92e268f1b3398eee7c8448a38d8dcec74c4&idx=1&mid=2247493287&scene=21&sn=b3aea273f52cc4b7d400fedb10918659#wechat_redirect
[800]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a084ddf529920f2572e843967c4cb4248b6adc854a001315dd54264080349d3a5cc2ea14&idx=1&mid=2247492983&scene=21&sn=5e0514b86258eebed20740dce83a6d45#wechat_redirect