测试，在DML操作的时候数据泵导出

最新推荐文章于 2022-05-11 15:50:17 发布

文档搬运工

最新推荐文章于 2022-05-11 15:50:17 发布

阅读量429

点赞数

分类专栏： EXPDPIMPDP

本文链接：https://blog.csdn.net/xxzhaobb/article/details/81267587

版权

EXPDPIMPDP 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

主要测试，当一个表有DML动作的时候，expdp会怎么样。
该测试主要开了3个窗口，一个插入数据，插入大约100W条，会比较久，第二个窗口查询插入的数据，当然查询到的不是100W条。第三个窗口在插入数据的时候，进行数据泵导出作业，看看能导出多少数据。
--结论
最终实验发现，在数据没有插入完毕，也就是还存在DML动作的时候，数据泵的导入已经完毕了。导出的数据肯定不是100W条。

create table t_test (x int) tablespace users;

session 1 ,插入数据

begin 
for i in 1..10000000
loop 
insert into t_test values(i);
commit;
end loop;
end;

-- session 2 查询数据

select count(*) from t_test;
ZBB@test>select count(*) from t_test;

  COUNT(*)
----------
     27031

ZBB@test>/

  COUNT(*)
----------
    114748

ZBB@test>/

  COUNT(*)
----------
    548374

ZBB@test>/  --expdp动作完成后，再次查询数据，发现DML动作并没有完成

  COUNT(*)
----------
   5620557

ZBB@test>

-- session 3 expdp导出t_test表

expdp \'/ as sysdba\' directory=dump,dumpfile=t_test_%U.dmp logfile=expdp_20180728.log tables=zbb.t_test

[oracle@test dump]$ expdp \'/ as sysdba\' directory=dump,dumpfile=t_test_%U.dmp                                                                              logfile=expdp_20180728.log tables=zbb.t_test

Export: Release 11.2.0.4.0 - Production on Sat Jul 28 17:09:12 2018

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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dump,dump                                                                             file=t_test_%U.dmp logfile=expdp_20180728.log tables=zbb.t_test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ZBB"."T_TEST"                              6.637 MB  774700 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/dump/t_test_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 28 17:14:02 20                                                                             18 elapsed 0 00:04:24

[oracle@test dump]$

--- 最后发现，数据还没有插入完毕， expdp就导出完毕了。所以，在更新一个表的时候，expdp导出表，数据还是有差异的。这样导出的数据也没有意义。还是在没有更新表的情况下expdp表数据，才有意义

-- 另外，如果在expdp的时候，dmp文件已经有了，就会出错。然后退出。

[oracle@test dump]$ expdp \'/ as sysdba\' directory=dump,dumpfile=t_test_%U.dmp logfile=expdp_20180728.log tables=zbb.t_test

Export: Release 11.2.0.4.0 - Production on Sat Jul 28 18:06:33 2018

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/u01/dump/t_test_01.dmp"
ORA-27038: created file already exists
Additional information: 1


[oracle@test dump]$

-- 加上REUSE_DUMPFILES这个参数，覆盖目标转储文件 (如果文件存在) [NO]。

expdp \'/ as sysdba\' directory=dump,dumpfile=t_test_%U.dmp logfile=expdp_20180728.log tables=zbb.t_test  REUSE_DUMPFILES=yes

[oracle@test dump]$ expdp \'/ as sysdba\' directory=dump,dumpfile=t_test_%U.dmp logfile=expdp_20180728.log tables=zbb.t_test  REUSE_DUMPFILES=yes

Export: Release 11.2.0.4.0 - Production on Sat Jul 28 18:26:06 2018

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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dump,dumpfile=t_test_%U.dmp logfile=expdp_20180728.log tables=zbb.t_test REUSE_DUMPFILES=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ZBB"."T_TEST"                              94.31 MB 10000000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/dump/t_test_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 28 18:29:18 2018 elapsed 0 00:03:06

[oracle@test dump]$

END