OraFAQ Forum: Server Utilities » Optimised solution for Data Migration

Home » RDBMS Server » Server Utilities » Optimised solution for Data Migration

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Optimised solution for Data Migration [message #208185]

Fri, 08 December 2006 08:48

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

Report message to a moderator

Re: Optimised solution for Data Migration [message #208204 is a reply to message #208185]

Fri, 08 December 2006 10:13

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.

Report message to a moderator

Re: Optimised solution for Data Migration [message #208271 is a reply to message #208185]

Sat, 09 December 2006 00:10

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

Report message to a moderator

Re: Optimised solution for Data Migration [message #208319 is a reply to message #208204]

Sat, 09 December 2006 07:36

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.

Report message to a moderator

Re: Optimised solution for Data Migration [message #208333 is a reply to message #208319]

Sat, 09 December 2006 10:47

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.

Report message to a moderator

Previous Topic:	SQL LOADER
Next Topic:	Running OS command using java procedure in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 13:57:19 CDT 2024