/
Expdp generates a line too long to run

Expdp generates a line too long to run

Problem Description

When we have a large schema  ie 1500 tables that require to use ONE-BY-ONE prepare method and data load method DATA PUMP (dp_exp). We are not able to run the APPLY.sh script & the exp_dp command as it fails due to length of line.

 Solution

If you are replicating a schema with a large# of tables and choosing ONE-BY-ONE prepare method and data load method DATA PUMP (dp_exp) you need to edit the config/*-setup.dbvrep script and set a max line length for the data pump commands

     Set _dpump_max_list_length=2000

 Add this setting which precedes the line “PROCESS PREPARE_DP SETUP CLEAR” in this file on a separate line, i.e. set to 2000 for example..

   SET _DPUMP_MAX_LIST_LENGTH=2000   

After this is done cd back to main directory & reun –all.sh (or –all.bat) script to get a new SCN & get the APPLY.sh to be recreated

$ cd ../

$ ./<env prefix>-all.sh (or all.bat)

 Alternatively with large schemas that you are replicating ALL TABLEs from the schema & it is a large number of tables and have chosen SINGLE_SCN prepare method with data load method Data Pump (dp_exp) you can modifiy the APPLY.sh (.bat) script to export by schema instead of the table list our script builds. The one KEY value you need to get from the *-all.log script is the FLASHBACK_SCN number that the schema was instantiated at. This will also be in the *.all.sh (or –all.bat) script.

 For example, original APPLY.sh (.bat) the expdp script is

expdp SYSTEM/manager@SOURCE dumpfile=exp_BRIAN.dmp directory=DATA_PUMP_DIR flashback_scn=197406000tables=BRIAN.SAMPHEADER,BRIAN.SAMPHEADER1,BRIAN.SAMP_RUNDETAILS,BRIAN.SAMP_RUNDETAILS1 logfile=exp_BRIAN.log JOB_NAME=DP_TRIAL_0001

… 

And you can modify the expdp command since you selected a full schema and SINGLE-SCN for a prepare method just DO NOT modify the flashback_scn from original APPLY.sh

expdp SYSTEM/manager@SOURCE file=exp_BRIAN.dmp directory=DATA_PUMP_DIR flashback_scn=197406000SCHEMAS=BRIAN logfile=exp_BRIAN.log

Related content

Error 9082 when running the *all.sh script
Error 9082 when running the *all.sh script
More like this
Datapump (impdp) with Oracle in 12c PDB
Datapump (impdp) with Oracle in 12c PDB
More like this
Oracle EXP Compatibility Matrix
Oracle EXP Compatibility Matrix
More like this
Create Oracle Data Pump script Manually
Create Oracle Data Pump script Manually
More like this
Create Oracle Data Pump script Manually
Create Oracle Data Pump script Manually
More like this
Create Oracle Data Pump script Manually
Create Oracle Data Pump script Manually
More like this