ORA-1555 insert table with 3 billions rows in Oracle with SQL

5 pts.
Tags:
ORA-1555
Oracle 9i
Oracle Tables
SQL
I have a big table with 3 billions rows and would like to rebuild in a less period of time as possible. I ran 3 plsql scrits , each plsql script is doing a full table scan to get 3 diferente range of data and insert in a new table , the commit in the cursor is for each 200.000 rows ( forall ) , after 16 hours I got the ORA-1555 , ( oracle 9.2 ) , but I am only doing a insert , I am alone in the database. How can I avoid the ORA-1555 in insert sql ?

Software/Hardware used:
Oracle9i solaris9

Answer Wiki

Thanks. We'll let you know when a new response is added.

The problem is probably being caused by the commits you are issuing every 200,000 rows.

When the number of transactions created after the cursor was opened increase, the chances of getting this error also grow if you continue fetching data from the cursor.

From the DBA perspective, maybe increasing the number and/or size of the rollback segments could help.

Another solution could be fetching the complete cursor into a collection, closing the cursor and then processing the rows, but it could not be an option when the amount of data being processed is that big.

In this case, maybe the best option could be modifying the cursor so it includes fewer rows so that you can fetch them all and close the cursor before you process them (and commit your changes). Then you open another cursor, fetch, close it and process the rows, and so on.

The main goal is not to fetch data from a cursor that was opened before some transaction was committed.

-CarlosDL

————————–

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following