Expdp generates a line too long to run

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

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