Writing Pl/Sql program using Table of records

[dsuva]

Hi,

I need to migrate a table from a production database into another production database. This table is a huge table, having around 98 columns, some 150 Million rows, 22 GB in size. doing a simple export / import is not feasible, with the condition of the server it is taking around 48 Hrs for export and more than 5 Days for import.

I need to write a PL/SQL script which will help fetch the data from one table and load it into the next table. I wish to get this work done by using a Procedure, and using a table of Records within because i have heard that Using a Table of Records can be very faster.

Since i am very new into PL/SQL programming, can you please provide me with any sample program which makes use of a Table of Records. To further speed up the process i would wish to pass parameters to the procedure such that it picks up rows say based on a low and high range of primary key values and then processes the rows into Table of Records and then does the insert.

Most information available on the Metalink site, i find very confusing because in the same program , apart from table of Records, REF cursors etc have been used.

Could someone be kind enough to provide me a sample program which would solve my purpose.

Thanks

[gbrabham]

Can you write the table out to a comma delimited text file and
import it in using DIRECT import ???

Other option ... can you do a create table as select ???

ensure you indexes on the new table are GONE ... build the indexes
after the fact...

[dsuva]

Hi ,

Thanks for the suggestion. Using SQL Load is the last option for me. Using direct load insert takes too much time. Around 10 Hrs. Therefore i am trying to do it using a PL/SQL procedure and using Table of Records. I have been told that this way i would save considerable time.

Thanks

[svk]

Do not be under any wrong impression.

Moving a 22GB table is not a 1hr job - unless, of course, you are running on h/w similar to the Earth Simulator.

What exp and imp options are you using ?
Have you considered transportable tablespace ? Is it feasible ?
What kind of h/w and disks do you have ?

[dsuva]

Hi,

Look Transportable tablespace won't solve my purpose because the data of this table must be loaded into the second table into the other database with some changes. Basically we are migrating a product into an upeer version, as a result the entire data needs to be ported with some changes. Hence i must use a PL/SQL construct to carry out the same activity.

Right now both the servers are different hence we are having to pull the data over a database link. Using transportable tablespace i can at the most bring over the table to this database, but i still need to do the migration using a script to populate the data into the new table. Currently the script i am using is taking arounf 10hours. But i am of the opinion that using Table of Records in my script i can have significance gain on time.

Thanks

[slimdave]

I don't think that PL/SQL will do anything for you here.

SQL will be faster -- you might consider sticking with your db-link and running multiple sessions from the target end to perform select from source, and insert /*+ append */ to the target with nologging set.