Home » RDBMS Server » Server Utilities » Optimised solution for Data Migration
Optimised solution for Data Migration [message #208185] Fri, 08 December 2006 08:48 Go to next message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

Hi All,

I want to know the best method used for data migration for Bulk of data in context of below scenerios...In all the cases, there is huge data millions n millions of rows to be migrated. The aim is to perform and complete the activity at the earliest/faster than any other.

Table structure is similar.

S1. Data has to be migrated from one table to another table(similar structure) within the same DB.

S2. From one DB to another DB.

S3. From one schema to another.


Also pls tell me the most common & optimized method usually DBAs adapt to perform such task.

Thanks
Rohan
Re: Optimised solution for Data Migration [message #208204 is a reply to message #208185] Fri, 08 December 2006 10:13 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
S1> insert /*+ append */ into t2
select * from t1;

S2> Use exp/imp utilities

S3> Use exp/imp utilities

If tables are huge the use transportable tablespaces.
Re: Optimised solution for Data Migration [message #208271 is a reply to message #208185] Sat, 09 December 2006 00:10 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
as suggested by "dba_blr" is good solution..
or with

you can used direct path with export for faster.
don't missed "fromuser or touser" clause with import.


regards
Taj
Re: Optimised solution for Data Migration [message #208319 is a reply to message #208204] Sat, 09 December 2006 07:36 Go to previous messageGo to next message
rohgupte
Messages: 7
Registered: November 2006
Location: Bangalore
Junior Member

Bulk insert is the same as you suggested for S1 as(insert /*+ append */ into t2 select * from t1;). Or Bulk insert is something else.


Re: Optimised solution for Data Migration [message #208333 is a reply to message #208319] Sat, 09 December 2006 10:47 Go to previous message
dba_blr
Messages: 43
Registered: December 2006
Member
Bulk insert is not exactly the same thing as insert /*+ APPEND*/ ...

The APPEND hint suggests oracle to ignore the FREELISTs and start adding the rows after the HWM.

Bulk insert reduces the no. of time the context switch happens between PL/SQL and SQL engine of oracle server. It achieves that by doing them in BULK as upposed to one at a time. It reduces the CPU usage considerably.

Both the mechanisms are certainly not the same as they work differently internally though their very purpose is to reduce load time.
Previous Topic: SQL LOADER
Next Topic: Running OS command using java procedure in Oracle
Goto Forum:
  


Current Time: Wed Dec 07 08:32:57 CST 2016

Total time taken to generate the page: 0.19259 seconds