The main test is what happens to expdp when a table has DML action.
This test mainly opens three windows, one is to insert data, about 100W pieces will be inserted for a long time. The second window queries the inserted data, of course, it does not query 100W pieces. In the third window, when inserting data, perform data pump export to see how much data can be exported.
-- Conclusion
Finally, the experiment found that when the data was not inserted, that is, there was DML action, the data pump import was completed. The exported data must not be 100W.
create table t_test (x int) tablespace users;
session 1, insert data
begin for i in 1..10000000 loop insert into t_test values(i); commit; end loop; end;
--session 2 query data
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 After the action is completed, query the data again and find DML The action is not complete COUNT(*) ---------- 5620557 ZBB@test>
--session 3 expdp export t'u test table
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]$
---Finally, we found that expdp was exported before the data was inserted. Therefore, when updating a table, when expdp exports the table, the data is still different. The exported data is meaningless. Only when the table data is not updated does it make sense
--In addition, if the dmp file already exists at the time of expdp, an error will occur. Then exit.
[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]$
--Add the parameter REUSE_DUMPFILES to overwrite the target dump file (if the file exists) [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