Re: Move data from production schema to development schema which has some

: Hi All,

: I am new to DB related development, I am currently asked to come up
: with a solution to write a script or use a tool to map tables between
: two schemas and copy data from one DB to another
: A simple scenario would be, we have our production DB with the current
: schema, we have a change in structure of development DB or just change
: names of some columns in some tables.
: the requirement is to have a script or a tool which can backup the
: production data and copy them to the new structure of development DB

: Is there any way i can export data from production system, and import
: them in the modified schema(which has some column names changed or
: tables added with null value).

: can anyone help me in this regard as I am totally blank on how to
: proceed with this.

I would copy the data before making the changes to the structure. Not only will that be easier, but it will also provide good test cases for when you test the change scripts on the development and/or test systems. (Eventually you will run those same scripts in production - right?)

You can do the copy in various ways, but if your general approach involves saving and loading the data (as opposed to copying it directly from one system to another) then it will be easier to restore the dev/test system(s) back to what they were before your changes - which may be useful if you need to run the change scripts more than once during their development.

As for the copy itself, there are various techniques. The export/import utilities are one way to do it. Some tools have menu options to copy data between systems (e.g. plsqldeveloper has commands to copy changes between systems, not sure about the data itself). You can write scripts that dump the data in the format of a series of INSERT commands, google for examples of these (I think Tom Kyte has a very generic one that works well).

There are various ways to copy the data without saving it. Create materialized views on the dev system using the prebuilt clause (using your existing tables), that can populate one system from another - then remove the materialized views, leaving the data behind to do with as you will. SQLPlus has (had?) a very useful COPY command. Also insert or "create table" using the select clause (and a database link) is useful for copying data, and any tools you have such as toad or plsqsldeveloper should be checked to see what options they provide.

To clear out tables before populating, you can use delete (which can be rolled back) or truncate [reuse storage] which is faster but can't be undone. The easiest way to avoid dependency problems is to ignore then and simply clear out all the tables until there are no dependency errors. Or look at the USER_DEPENDENCIES table to figure out that order to delete the data without getting errors. Same thing when restoring the data. It is nicest to load the data in the right order, but as long as you are not generating sequence numbers then you can simply run the insert scripts multiple times until you have no dependency errors (though you will get lots of duplicate errors).

$0.10 Received on Tue Jan 19 2010 - 10:01:42 CST