Issues with updating many rows oracle Chatting to sexy women without registration
We wrote a custome procedure which opens a simple cursor and reads all the 58 million rows from the SOURCE Table and in a loop processes the rows and inserts the records into a TARGET Table. BULKLOAD", line 66 ORA-06512: at line 1 We got the same error even with 1 million rows. src_cpd_dt_array.count INSERT INTO ima_dly_acct ( CPD_DT, ACQR_CTRY_CD, ACQR_TIER_CD, ACQR_PCR_CTRY_CD, ACQR_PCR_TIER_CD, ISSR_BIN, OWNR_BUS_ID, USER_BUS_ID, MRCH_LOCN_REF_ID, NTWRK_ID, STIP_ADVC_CD, AUTHN_RESP_CD, AUTHN_ACTVY_CD, RESP_TM_ID, PROD_REF_ID, MRCH_REF_ID, ISSR_PCR, ISSR_CTRY_CD, ACCT_NUM, TRAN_CNT, USD_TRAN_AMT) VALUES ( src_cpd_dt_array(j), src_acqr_ctry_cd_array(j), null, src_acqr_pcr_ctry_cd_array(j), null, src_issr_bin_array(j), null, null, src_mrch_locn_ref_id_array(j), src_ntwrk_id_array(j), src_stip_advc_cd_array(j), src_authn_resp_cd_array(j), src_authn_actvy_cd_array(j), src_resp_tm_id_array(j), null, src_mrch_ref_id_array(j), src_issr_pcr_array(j), src_issr_ctry_cd_array(j), src_acct_num_array(j), src_tran_cnt_array(j), src_usd_tran_amt_array(j)); COMMIT; END bulkload; / SHOW ERRORS -------------------------------------------- good gosh -- you aren't serious are you??? Also, I utterly fail to see how you could use rownum to limit rows with bulk binds, it it not possible. You want to "stream" data -- get some, process some, write some, get some, process some, write some.
The logic works fine but it took 20hrs to complete the load. We declared PL/SQL BINARY_INDEXed Tables to store the data in memory. We do have the following configuration: SGA - 8.2 GB PGA - Aggregate Target - 3GB - Current Allocated - 439444KB (439 MB) - Maximum allocated - 2695753 KB (2.6 GB) Temp Table Space - 60.9 GB (Total) - 20 GB (Available approximately) I think we do have more than enough memory to process the 1 million rows!! Use the LIMIT clause, bulk collect say 100 to 1000 rows -- process them, bulk insert them, get the next 100/1000 rows. Your process got bigger then your OS would allow you (you hit an OS limit, might be ulimit related or whatever). LIMIT clause will do one fetch less if the last fetch has less then [limit] rows. You don't want to GET ALL, process all, WRITE ALL and flood one thing or the other -- do a bit, process a bit, write a bit, start over.
Oracle's PL/SQL language has two basic mechanisms for getting data from the database: SELECT and cursors.
So, rownum changes the answer -- limits total number of rows.
To me -- 1000 rows in an array fetch, based on experience -- past performance -- memory usage -- it 10 times larger then I would recommend.
December 09, 2002 - am UTC rownum would limit the TOTAL NUMBER OF ROWS IN THE ENTIRE RESULT SET.
When you use SELECT in a PL/SQL block, it's important to make sure that one row will always be returned by your query.
If more than one row is returned, the TOO_MANY_ROWS exception occurs.
Listing A shows an example from Oracle's HR sample schema: There is more than one employee with the last name King, so the script fails.