data conversion advises

[mike2000]

we are in the process of doing data conversion for several client and this process will continue repeatly so I am looking for the some advises of how to do this in the best way:

basically, we have one table with couple of million rows or probably more or could be less so depend. I need to get the data from this table and insert into another table; however, I am trying to stay away of the usage of rollback as much as possible (I don't want to set the transaction to use the big rollback b/c the client might not have it). I could put it into the procedure where I can commit every 1000 rows or so but I try not too as well. In general, I am trying to find the way to commit every 1000 rows or so in a simple SQL (insert into and select from)


any advises???

[LKBrwn_DBA]

export the table
then
import with commit=y

[slimdave]

Originally posted by mike2000
I could put it into the procedure where I can commit every 1000 rows or so but I try not too as well. In general, I am trying to find the way to commit every 1000 rows or so in a simple SQL (insert into and select from)

That would almost surely raise a "snapshot too old" error.

How about a nologging insert, using APPEND or a CTAS statement?

[Mr.Hanky]

Originally posted by LKBrwn_DBA
export the table
then
import with commit=y

Actually without specifying the BUFFER= this will attempt to load the whole table, depending on your default buffer size.

You could do as Dave says or use...


COMMIT=Y
BUFFER=(pick a small size, maybe 1000000) that is less than 1MB
Inside of an import .par file. This will still generate some redo but it will be minimized.

MH

[svk]

Import may not be the solution if the table names need to be different.

A PL/SQL with commit after 1000 (or so) rows should be okay. And, it will NOT cause "snapshot too old" error.

There can be better ways of doing it based what you "actually" need to do. Is there any data related activities involved ?

You can always create a separate tablespace for a big rollback segment and drop it after the job is done.

[slimdave]

A PL/SQL with commit after 1000 (or so) rows should be okay. And, it will NOT cause "snapshot too old" error.

Are you saying that intermediate commits within a select cursor do not provoke "snapshot too old" errors

[stmontgo]

Originally posted by slimdave
Are you saying that intermediate commits within a select cursor do not provoke "snapshot too old" errors

ditto, the more you commit the better the chance of 0ra-1550,

more to the point, will a transportable ts suit your needs?

steve

[svk]

Intermediate commits within a select cursor sure cause snapshot too old errors. But not when the commits don't do anything to the table from which the data is being SELECTed. Here in this specific case, we are talking @ copying from a table to another table and the commits are for that "another table".

BTW, COPY command is another solution.

When I said "there can be better ways", I wanted to mention transportable tablespace. But, I thought proposing that was a little too early based the information available.

[mike2000]

thanks all for the input.

I thought I have mentioned this before: Writing procedure to commit every 1000 rows or so is out of the picture. Exp/imp is not an option here. I know these two options is the best to eleminate problems with rollback but surely it's not option when we convert the data. Creating a big rollback and force it to use this big rollback is not an option as well.

what I am looking for is something like: I have a table name TEST with name and address is the column, and I want to have another table named TEMP with name and address is column in addition I have another column with sequence so when I write an insert to the new table, I can break into small piece let say:

insert into NEW_TABLE
select * from TEMP
where num in(1,1000)

something like that.


this is the way the management want to approach and I hope you know what I mena

[slimdave]

Originally posted by svk
Intermediate commits within a select cursor sure cause snapshot too old errors. But not when the commits don't do anything to the table from which the data is being SELECTed.

No, not so, i think.