Re: Transaction segmentation

Michael Bierenfeld a écrit:

> Khemraj DOOKHEE wrote:
> >
> > Hi everyone,
> >
> > I would like to segment a big Oracle transaction (such as an insert of a
> > million records into a table) into smaller ones.
> >
> > I tried to execute my big transaction, but it failed due to a rollback
> > segment error. I do not want to alter any rollback segments, so I
> > suppose the only solution left to me is to try to cut the transaction
> > into smaller ones and then execute them one after the other.
> >
> > The problem is that I do not know how to perform such an operation, that
> > is to segment a transaction into several smaller ones.
> >
> > By the way, I've got another question. How can we affect a reasonable
> > value for the INITIAL parameter of a rollback segment ? Is it by trial
> > and error ?
> >
> > Any help would be much appreciated.
> >
> > Thanks,
> >
> > Khemraj.
>
> Hello,
>
> this strongly depends on the type of your transactions. Millions of rows
> ? Are they comming from a different table or a text-file or whatever.
>
> There are several possibilities :
>
> - Use PL/SQL to copy from one table to another and perform commits after
> each xxxx inserts
> - Use the copy command in SQL/Plus with "copy commit xxx" where xxx is
> the number of array binds
> - Use SQL/load
>
> Could you send an example of those transactions.

Thanks Michael,An example would be the following: Let A and B be two identical users originating from two distinct database instances I1 and I2. My transaction consists in transfering the content of a certain table (let's say TableX (id1 number, id2 number, id3 number, id4 number, primary key (id1, id2, id3, id4)) from user A to user B. B's TableX does contain a certain amount of data before the transfer. I cannot simply transfer the content of A's TableX (which contains at least 800000 rows) into B's TableX as I run a risk of violating the primary key constraint. I have decided to proceed in two steps :

1. I start by creating an identical user C in I2 and I import the content of A into C by using Oracle's Imp/Export utilities (I do this because I cannot directly alter A's data)
2. I then modify the records of C's TableX so that to make sure that when I insert them into B's TableX I won't face any constraint violation. Once this is done, I start the transfer by calling the following : "INSERT INTO A.TableX SELECT * FROM C.TableX" and I do a commit only at the end of this operation.

I started looking for solutions to my problem and the first posssibility suggested by Michael is what I found and it is interesting to me as I work with PL/SQL. My solution for committing after each xxx inserts will most probably consist in making use of the virtual column ROWNUM when fetching records. I'll have to work on it.

Any help would be most welcomed.

Best regards,

Khemraj. Received on Fri May 07 1999 - 04:04:12 CDT