title: Load multiple data files into multiple tables using single control file using sql loader
body: <p>I have requirement to load billions of record into 5 different tables , each one of these tables have different data files. These 5 tables will be populated daily and will be truncated next day before loading fresh data.</p>
  
  <ul>
  <li>Que1 : How do I load data into 5 different table using 5 different data files using 1 control fle?</li>
  <li>Que2: Do I need 5 different discard, log and bad files to keep track of these 5 different loads?</li>
  <li>Que3 : What is better and efficient way to load billions of records daily - using 5 different control table , 5 discard ,5 log file OR just 1 control table will solve the purpose.</li>
  <li>Que4: What if one of 5 load fails then I need to rerun the sqloader for all 5 tables again?</li>
  </ul>
  
  <p>Note : As of now we are loading data into one table but it is taking 5-6 hours to load, so we are looking for better performance. 
  I will be running sqlldr from shell script.</p>
  
  <p>there are 4 different data files containg data for 1day, 7day, 15day </p>
  
  <pre><code>LOAD DATA
  replace
  INTO TABLE T1_1DAY_STG
  FIELDS TERMINATED BY X'05'
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  (
  MM_INTERVAL,
  STRATEGY_ID ,         
  AGGREGATE_DATE           date "YYYY-MM-DD"
  )
  INTO TABLE T1_7DAY_STG
  FIELDS TERMINATED BY X'05'
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
   (
   MM_INTERVAL,
   STRATEGY_ID ,         
   AGGREGATE_DATE           date "YYYY-MM-DD"
    )
  </code></pre>
  
  <p>I am planning for shell script like this </p>
  
  <pre><code>echo "start SQL loader" &gt;&gt; ${LOG_FILE} 2&gt;&amp;1
  DCTL=$( eval echo \${TX_SQLLDR_${i}_CTL_SP} )
  DDATA=$( eval echo \${TX_${i}_DATA_FILE_SP} )
  DLOG=$( eval echo \${TX_${i}_DATA_FILE_LOG_SP} )
  DBAD=$( eval echo \${TX_${i}_DATA_FILE_BAD_SP} )
  DDISCARD=$( eval echo \${TX_${i}_DATA_FILE_DISCARD_SP} )
  
  ${ORACLE_HOME}/bin/sqlldr ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID}   control=${CTL_DIR}/${DCTL} data=${DATA_DIR}/${DDATA} log=${LOG_DIR}/${DLOG}   bad=${LOG_DIR}/${DBAD} discard=${LOG_DIR}/${DDISCARD} errors=${ERRNUM} direct=true silent=FEEDBACK &gt; ${TMP_LOG_FILE} 2&gt;&amp;1
  </code></pre>
  
  <p>Thanks
  Sandy</p>