Different Results Between QUERY Parameter Used With EXP/EXPDP and SQL*Plus
(Doc ID 757701.1)
Last updated on SEPTEMBER 20, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
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 <username/pwd>
drop table <table name>;
purge recyclebin;
create table <table name>
(
id number,
t_stamp timestamp(6)
);
insert into <table name> values (1, to_timestamp ('01.12.2008 10:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 11:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 12:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 13:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 14:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
commit;
connect <username/pwd>
drop table <table name>;
purge recyclebin;
create table <table name>
(
id number,
t_stamp timestamp(6)
);
insert into <table name> values (1, to_timestamp ('01.12.2008 10:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 11:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 12:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> values (1, to_timestamp ('01.12.2008 13:15:20.123000', 'DD.MM.YYYY HH24:MI:SS.FF6'));
insert into <table name> 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 <username/pwd> file=<file name>.dmp tables=<table name> 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 <table name> 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 <table name> 0 rows exported
Export terminated successfully without warnings.
Export the table with DataPump export:
#> expdp <username/pwd> directory=<dir name> dumpfile=<file name>.dmp tables=<table name> 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=<dir name> dumpfile=<file name>.dmp tables=<table name> 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"."<table name>" 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\<file name>.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=<dir name> dumpfile=<file name>.dmp tables=<table name> 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"."<table name>" 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\<file name>.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 16:37:28
Start the same statement in SQL*Plus:
SQL> select count (*) from <table name> where t_stamp > current_timestamp - 21;
COUNT(*)
----------
5
1 row selected.
COUNT(*)
----------
5
1 row selected.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |