Different Results Between QUERY Parameter Used With EXP/EXPDP and SQL*Plus
Last updated on NOVEMBER 28, 2016
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]Information in this document applies to any platform.
***Checked for relevance on 23-Apr-2013***
Symptoms
You try to export parts of data in a table using the parameter QUERY and observed that between the count delivered by original export (or DataPump export) is different from the count obtained when the same query is directly started against the table via SQL*Plus.
Steps to reproduce the problem:
--create and populate the table
connect test/test
drop table tab001;
purge recyclebin;
create table tab001
(
id number,
t_stamp timestamp(6)
);
insert into tab001 values (1, to_timestamp ('01.12.2008 10:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 11:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 12:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 13:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 14:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
commit;
connect test/test
drop table tab001;
purge recyclebin;
create table tab001
(
id number,
t_stamp timestamp(6)
);
insert into tab001 values (1, to_timestamp ('01.12.2008 10:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 11:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 12:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 13:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into tab001 values (1, to_timestamp ('01.12.2008 14:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
commit;
Export the table with original export:
#> exp test/test file=tab001.dmp tables=tab001 query=\"where t_stamp > current_timestamp - 21\"
returns:
Export: Release 11.1.0.7.0 - Production on Mon Dec 22 16:21:47 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TAB001 0 rows exported
Export terminated successfully without warnings.
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Produc tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TAB001 0 rows exported
Export terminated successfully without warnings.
Export the table with DataPump export:
#> expdp test/test directory=dpu dumpfile=tab001.dmp tables=tab001 query=\"where t_stamp > current_timestamp - 21\"
returns:
;;;
Export: Release 11.1.0.7.0 - Production on Monday, 22 December, 2008 16:37:08
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dpu dumpfile=x111.dmp tables=tab001 query="where t_stamp > current_timestamp - 21"
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 "TEST"."TAB001" 5.421 KB 0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
D:\DATABASES\O111\DPU\TAB001.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:37:28
Export: Release 11.1.0.7.0 - Production on Monday, 22 December, 2008 16:37:08
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=dpu dumpfile=x111.dmp tables=tab001 query="where t_stamp > current_timestamp - 21"
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 "TEST"."TAB001" 5.421 KB 0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
D:\DATABASES\O111\DPU\TAB001.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:37:28
Start the same statement in SQL*Plus:
SQL> select count (*) from tab001 where t_stamp > current_timestamp - 21;
COUNT(*)
----------
5
1 row selected.
COUNT(*)
----------
5
1 row selected.
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms