Home » RDBMS Server » Backup & Recovery » selective data transfer from old to new database (oracle database 9i on solaris 9)
selective data transfer from old to new database [message #326441] Wed, 11 June 2008 09:24 Go to next message
pratmh
Messages: 9
Registered: December 2007
Junior Member
Hi Experts,
I need help of you guys for a migration activity. We have an older version of an application with an in-built database containg data over the last many years. Now a new version of the application has been installed on a new box which has the new inbuilt 'empty' database. This new database is having some new tables and also some of the older tables having a little diffrent structure like some extra columns or a changed datatype. Rest of the entire database structure (almost 80% of original database) is same as before. There are 2-3 main schemas in the application which contains all these tables. Now the challenge is that we can't use export-import as the same would destroy the consistency of the structure of the tables in new database. Also we need to take a call on new columns, i.e. if they are required to be filled with NULL or some particular values. For all this, we need to make a comparison of all the older and new tables at the column level. Once done, we might use the database links to populate the new tables.
However this would include a lot of scripting work. Could someone here has faced a similar scenario? If yes, please share your experiences and/or scripts used to resolve the same. Your comments/suggestions are highly appreciable.
Regds
PM
Re: selective data transfer from old to new database [message #326449 is a reply to message #326441] Wed, 11 June 2008 09:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>export-import as the same would destroy the consistency of the structure of the tables in new database
You can avoid it.
import the 'old database schemas' into the new database, but in a different schema (with a different tablespace so that you can drop it later).
Scripting is unavoidable here.
It would be much easier to compare the schemas within the same database.
There are tools like TOAD with options to compare two different schemas, which might give you a good start.
Re: selective data transfer from old to new database [message #326647 is a reply to message #326449] Thu, 12 June 2008 02:49 Go to previous message
pratmh
Messages: 9
Registered: December 2007
Junior Member
Hi,

Exporting and importing into a new schema would not serve us any purpose as the task of copying data into new database schemas would not be cut short by any way. If someone could help with some sample scripts, it would be highly appreciated.

Regds
Prateek
Previous Topic: re-store offline backup
Next Topic: Undo db changes of particular user in particular date
Goto Forum:
  


Current Time: Sat Dec 03 12:13:00 CST 2016

Total time taken to generate the page: 0.09272 seconds