Test, data pump export during DML operation

Keywords: Oracle Session Database Windows

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

Posted by danville on Wed, 29 Jan 2020 08:02:52 -0800