代码改变世界

EXP&EXPDP导出数据同步问题

2013-03-19 09:59  潇湘隐者  阅读(2137)  评论(0)  编辑  收藏  举报

在itpub论坛上看到一个问题：如果库正在被写，exp是不是导出的数据就是不同步的了？ http://www.itpub.net/forum.php?mod=viewthread&tid=1772882&page=1#pid21172716

按照推理逻辑，exp导出的数据应该不是同步的，也不可能同步，除非exp能智能判断数据库是否正在写操作，而且一直等到写操作结束为止。显然这些假设都不合理。下面我们来做个实验，验证一下exp导出数据是否同步。

新建一个测试表TEST， 我们写一个循环，往表里面插入1000条记录，而且每插入插入一条记录，停顿0.5秒。这样来模拟数据库处于写状态。

CREATE TABLE TEST
(
       ID           NUMBER ,
       UPDATE_DATE  DATE   
)


BEGIN

    FOR V_INDEX IN 1 .. 1000 
    LOOP
          INSERT INTO TEST
          VALUES(V_INDEX, SYSDATE);
          
          COMMIT;
          
          DBMS_LOCK.SLEEP(0.5);

   END LOOP;
END;

执行上面脚本，然后我们执行导出表TEST的exp命令，如下所示：


[oracle@DB-Server ~]$ exp userid=etl/***@BIWG tables=test file=test.dmp buffer=4096000 feedback=10000 log=test.log

Export: Release 10.2.0.1.0 - Production on Mon Mar 18 17:48:39 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST  　　　　11 rows exported
Export terminated successfully without warnings.

 


[oracle@DB-Server ~]$ exp userid=etl/***@BIWG tables=test file=test.dmp buffer=4096000 feedback=10000 log=test.log

Export: Release 10.2.0.1.0 - Production on Mon Mar 18 17:48:49 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST  30 rows exported
Export terminated successfully without warnings.

 


[oracle@DB-Server ~]$ exp userid=etl/***@BIWG tables=test file=test.dmp buffer=4096000 feedback=10000 log=test.log

Export: Release 10.2.0.1.0 - Production on Mon Mar 18 17:48:59 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table TEST  　　　　　　50 rows exported
Export terminated successfully without warnings.

显然从上面实验来看，可以验证我们的想法:exp导出数据应该是某个时间点的数据，数据是不同步的。

下面我们先截断表TEST的数据，然后重新执行上面SQL脚本，然后来验证一下expdp导出数据是否同步：

[oracle@DB-Server ~]$ expdp userid=etl/***@BIWG directory=DUMP_DIR dumpfile=test1.dmp tables=test logfile=test.log

Export: Release 10.2.0.1.0 - 64bit Production on Monday, 18 March, 2013 18:17:44

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ETL"."SYS_EXPORT_TABLE_01": userid=etl/********@BIWG directory=DUMP_DIR dumpfile=test1.dmp tables=test logfile=test.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ETL"."TEST" 8.039 KB 　　　　　　 187 rows
Master table "ETL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ETL.SYS_EXPORT_TABLE_01 is:
H:\DATAPUMP\TEST1.DMP
Job "ETL"."SYS_EXPORT_TABLE_01" successfully completed at 18:29:40

 

 

[oracle@DB-Server ~]$ expdp userid=etl/***@BIWG directory=DUMP_DIR dumpfile=test2.dmp tables=test logfile=test.log

Export: Release 10.2.0.1.0 - 64bit Production on Monday, 18 March, 2013 18:20:07

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Starting "ETL"."SYS_EXPORT_TABLE_01": userid=etl/********@BIWG directory=DUMP_DIR dumpfile=test2.dmp tables=test logfile=test.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ETL"."TEST" 17.14 KB　　　　　　 770 rows
Master table "ETL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ETL.SYS_EXPORT_TABLE_01 is:
H:\DATAPUMP\TEST2.DMP
Job "ETL"."SYS_EXPORT_TABLE_01" successfully completed at 18:33:46

显然通过上面实验测试，发现expdp导出数据也是不同步的。