Home » RDBMS Server » Server Utilities » Transferring changed data from Database A to B by Data pump (Oracle 11.1.7.0 on windows 2008 server 64 bit)
Transferring changed data from Database A to B by Data pump [message #501929] Fri, 01 April 2011 09:14 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
I have database A (Working in Live environment)
and Database B copy of Database (Not live)
I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B

There are around 20 schema

If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.

IF i do following in source database

expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE

AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes

Report message to a moderator

Re: Transferring changed data from Database A to B by Data pump [message #501931 is a reply to message #501929] Fri, 01 April 2011 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

What about records that were DELETEd from database A?
These records remain in database B.

Report message to a moderator

Re: Transferring changed data from Database A to B by Data pump [message #501933 is a reply to message #501929] Fri, 01 April 2011 09:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>will it add new data and update existing one in table and not touch the table structure and indexes

No.
Incremental export/import will not work or exist.
Import will always try to append the data by default.

>>want to import only updated and new records in the table in Database B
You cannot do it with import.

Report message to a moderator

Re: Transferring changed data from Database A to B by Data pump [message #501934 is a reply to message #501929] Fri, 01 April 2011 09:27 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
If i use TABLE_EXISTS_ACTION=truncate, will it delete data or truncate table before loading data in the table

expdp directory=dpump_dir dumpfile=table_data.dmp
TABLE_EXISTS_ACTION=truncate
content=data_only
schemas=ACCMAIN,HRMAIN,.....
include=TABLE

Because if it deletes the data, then lot of redolog will be generated

Report message to a moderator

Re: Transferring changed data from Database A to B by Data pump [message #501935 is a reply to message #501934] Fri, 01 April 2011 09:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Because if it deletes the data, then lot of redolog will be generated
So?
Nothing is free.
You can look into some kind of CDC mechanism/Streams. Again, this is not free Smile

Report message to a moderator

Re: Transferring changed data from Database A to B by Data pump [message #501939 is a reply to message #501935] Fri, 01 April 2011 10:07 Go to previous message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
It means by using following

I can export whole data of table of specified Schemas and it will only unpopulate and then populate the table data and will not disturb any other objects in those schema in Database B like Database link, procedure,packages,functions,triggers and will not drop indexes.


expdp directory=dpump_dir dumpfile=table_data.dmp
TABLE_EXISTS_ACTION=truncate
content=data_only
schemas=ACCMAIN,HRMAIN,.....
include=TABLE


I can export whole data of table of specified Schemas and then import in Database with same structure and tables and will not make any structural change

[Updated on: Fri, 01 April 2011 11:08]

Report message to a moderator

Previous Topic: SQLLOADER: views linked to External tables
Next Topic: SQLLoader: Condition on field [country Like '%String%']
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 11:41:56 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.