| Big data Inserts [message #188465] |
Fri, 18 August 2006 12:47  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Dear ALL,
Here is my concern. We have a huge table say >130GB and we need to keep a copy of it:exactly same data for one time. we will call this table:Old_table. It has to be done within the database meaning no exports and Imports possible since the user would not have access to the OS to build the dump files.
Is the insert into old_table select * from ... with append/ parallel hints the only option for this data copy. I am wondering whether the inserts could create trouble for undo segments since only one commit would be done after last rows successfully inserted. However my feeling is that, inserts always generate very low undo. What the other options you guys could think to work out in this case.
Has any one done any work with bulk inserts and do they have any advantage over serial append hinted inserts. Any help would be highly appreciated.
thanks.
|
|
|
|
| Re: Big data Inserts [message #188483 is a reply to message #188465] |
Fri, 18 August 2006 13:29  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
How about:
Begin transaction;
CREATE TABLE old_table AS SELECT *
FROM your_table AS OF TIMESTAMP '2002-02-05 14:15:00';
commit;
|
|
|
|
| Re: Big data Inserts [message #191827 is a reply to message #188465] |
Fri, 08 September 2006 05:03 |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
Hi
No details about Your system and the table, so I assume 10gR2, and no lobs etc...
You have many options. If this is really huge, parallel could be very nice.
Using flashback, would require that Your database has enough undo to hold all changes from start to end of Your load.
Another approach could be to LOCK the table, to prevent any changes from occurring, and then insert say 1 mio rows at a time, if much undo/redo is generated.
But why not try it out, to see what is feasible on Your system?
Br
Kim
|
|
|
|
|
|
| Re: Big data Inserts [message #191896 is a reply to message #188465] |
Fri, 08 September 2006 09:17 |
goudelly
Messages: 52
Registered: August 2006
Location: India
|
Member |
|
|
Hi,
You can use the utl_file utility to take the comaplete rows from the table to a OS level falt, test or csv file. The total records will come out from oracle table to OS level file.
And also you can insert the rows into oracle tables from a flat file. If you want your table should partioned and store the data into physially and logically seperatly. You have use the sqlloader.
If you want we have solution.
Thanks,
Mohan Reddy G.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
