The QUERY
parameter is used to restrict data in the export job. If you want to export number of rows that are exist in another table then you can use subquery in QUERY
parameter.
If you want to join two tables and export you can create table by joining two tables. As shown below.
SQL> create table export_tbl as select e.first_name FN, d.department_name DN from employees e join departments d on(e.department_id=d.department_id);
Table created.
Then can export it.
[oracle@testsrv Desktop]$ expdp hr/hr directory=user_dir dumpfile=export.dmp tables=export_tbl logfile=export.log
Alternatively, you can create a view by joining tables and export views(INCLUDE=VIEWS
). You have to filter views during import.
The cause of the ORA-01555
error is undo_retention
parameter has the lower value as compared to the time taken to complete the export with the parameter consistent=y
(which is deprecated in 11g and should use flashback_time
or flashback_scn
).
Your undo retention should able to retain the undo information for the time period of export job.
For example, if you export starts at 00:00 AM
and it takes 2 hours to complete then the undo_retention
should be set at least 2 hours if you wish to get consistent data.
I have simulated the problem in my test server.
[oracle@orcl expdp_dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 8 09:41:40 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 2400
undo_tablespace string UNDOTBS1
My undo retention is set to 40 minutes.
SQL> select bytes/1024/1024 from v$datafile where ts#=2;
BYTES/1024/1024
---------------
32764
And my undo tablespace is 32GB large.
I tried to export one of my schemas.
expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" completed with 1 error(s) at Tue
Feb 7 22:14:46 2017 elapsed 0 01:14:39
It took 1 hour and 14 minutes. And there were moderate DML operations in the database.
ORA-31693: Table data object "MYSCHEMA"."MS_TABLE":"MS_TABLE."MS_TABLE_B_2005" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 84 with name "_SYSSMU84_1745672109$" too small
. . exported "MYSCHEMA"."PRORATE_EVENT" 8.789 KB 4 rows
. . exported "MYSCHEMA"."PRORATE_RULE_GROUP" 5.945 KB 1 rows
. . exported "MYSCHEMA"."ORACLE_JOB_METADATA" 0 KB 0 rows
And I got the ORA-0155
error.
Then I increased the undo retention to 1 hour and 20 minutes.
SQL> alter system set undo_retention=4800;
System altered.
And again I tried to export the schema.
expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" successfully completed at Wed Feb
8 22:15:52 2017 elapsed 0 01:15:45.
And there was no ORA-01555
anymore.
Total estimation using BLOCKS method: 182.9 GB
and the actual file size is 141 GB.
Why the difference in estimation and the actual size of the dump file? Look at this post for the answer.
Regarding the time taken to complete the export. Try to use the following parameters.
Parallel
DIRECT=y
Also, try to gather data dictionary statistics before starting the export job-
SQL> connect / as sysdba
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.lock_table_stats (null,'X$KCCLH');
SQL> exec dbms_stats.gather_fixed_objects_stats;
Futher Readings:
Poor Performance With DataPump Export On Large Databases (Doc ID 473423.1)
Data Pump Export
Best Answer
I could not get the syntax you used working. We typically provide a
WHERE
clause inQUERY
. Example:Then export:
If IAO.ELIJ really exists, you could use something like this: