CODEWITHSUNDEEP
oracle-databaseoracle11gdata-migration
Spring
Spring

Reputation: 11835

Oracle data migrating within different schemas

In my Oracle 11 database I have two schemas with slightly different datamodels (target schema is empty also column and table names are in a different language)

I want to migrate data from one to another. I have around 15 tables, So the column and table names, needs to be mapped within each other, some tables needs to be merged into 1 new table in target schema etc and then migrate data.

Do I have to do this manually with scripts, or there is some help I can get from Oracle SqlDeveloper? or anything else to help mapping?

Upvotes: 0

Views: 1479

Answers (2)

Guido Leenders
Guido Leenders

Reputation: 4262

It will involve some manual work, either through comparing two repositories with a home brewn script or a good CASE tool.

Or use Dell's TOAD. The DBA option of TOAD allows you to compare two schemas and generates scripts which change schema 1's structure into schema 2's structure (or otherwise around). See TOAD documentation

When it is one time exercise, I would use TOAD to generate the SQL and manually adapt it where necessary.

When it is a recurring problem, you might want to develop your own scripts or purchase a good starting point.

Upvotes: 1

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

Since table and column names need to be mapped, this surely must involve some manual work. Personally I would just use plain SQL to map the structure and copy data, as you were already suggested, especially if target database is empty - so it's just a simple insert.

If you want a tool for this, you can try dbForge Data Compare. I haven't used it, so I can't say for sure, but on the features page it states that:

  • User mapping feature allows comparing tables with different names and column set
  • Map one table in source with two or more tables in target (useful when table was split)

so maybe it can help you.

Upvotes: 2

Related Questions