my company is on its way to perform huge modifications within the ER-structure, in order to create a company-wide unified data model. In fact, we are about to create an optimized and normalized data model / ER-diagram to improve performance, consistence and efficiency. Now to my questions:
- we will have to have the original data structures running parallel to the new tables and constructs. Are there any experiences in doing so? How was ist realized?
- we will have to migrate data from old structures and tables into new ones (means no 1:1-migration but evtly. splitting one table into three, four, ...). Is there any tool to support this within Oracle 9i?
- does anyone know some tools to simulate synchronous data access on a testing system? Means: creating DB-connects, continous data access/manipulation etc.
Yes, you can have the old schema and the new schema in your database at the same time, no problem. Just make sure they are different schemas (user accounts).
If you have public synonyms that is OK too, but makes life a bit more challenging. The public synonyms will continue to point to the old schema and not conflict with the new schema as far as end users are concerned. However, when you are migrating data your code should use absolute references such as "INSERT INTO SCHEMA2.MYTABLE AS SELECT * FROM SCHEMA1.MYTABLE;" to skip synonyms. Once you are done migrating, you just drop and recreate the public synonyms to point to the new schema.
You are not alone in wanting a good tool to do the job for you. There is almost never a one-to-one mapping of an old schema to a new schema, and tables getting split or combined is a common headache.
Do a Google for data migration tools and you might find more than one. However, even tools require a lot of detailed hands-on mapping before they can run. Migration can be very frustrating, but it keeps the money flowing in our direction. Enjoy difficult tasks, they pay the rent.