Data Exp imp

FreddyFrancis

FreddyFrancis

Well Known Member
Hello List,

We have a table with 4 million records. THe table structure is getting changed and hence data needs to be backed up and then put back into the table with the new structure.

the database being Oracle 10g

What options do we have so that we do the process in least possible time.

insert into T2 c1,c4,c2,c3 select c1,0,c2,c3

this can be done if the data is small.. but for the amount of data i menationed above this would take timee.

There are business reason why we are forced to change this table.

Regards,
Freddy
 
Freddy,

I've done this before using the SQL method you showed. I don't think you're going to find a faster method than that. You can just try altering the table directly using DDL SQL to add the column and adding just the definition of the new column into JDE. Although theoretically this should have worked JDE seemed to have issues with numeric columns that it didn't create . . . So I ended up using SQL to copy the data into a new table from the original, renaming the original table, regenerating the original table in JDE (with changes) then again using SQL to insert data into the re-generated table from the copy.
 
What may also help is dropping indexes prior to the copy and letting them rebuild after the copy is complete. That tends to take far less time for some of our bigger (10's of millions of rows) tables than letting the indexes build during the copy.
 
Thanks a lot for thise suggestions.

Has anyone tried using Oracle Bulk load ..?

Regards,
Freddy
 
Freddy,

Will 4 million rows really take that long?

executing the statement

CREATE TABLE TESTXXX AS SELECT * FROM TESTDTA.F0902;

took approximately 6 seconds for 1,156,122 rows. Granted there were no indices in the created table, but how long is too long for you?
Is 60 seconds too long?
 
Hello Francis,

If SQL doesn't work then try Oracle's Data Export/Import tool.
 
Oracle 10g comes with new tool for export import which works with Oracle Loader.

Here are command
Create Dump Directory
expdp to take table dump
impdp to import table.


There are good handly tools in market to took export dump in text file also. SQLWAYS is one of those tool.


Excel 2007 can handle Large number of Records. I didnt remem exact rows but it have consuderably increased then excel 2003. You can then import back using Access ODBC to Oracle Table.
 
Further to what others have said, don't ignore the mechanical. Make sure the destination tablespace has enough pre-allocated space before starting as disk file growth will really slow you.
 
Back
Top