Can I use dbms_redefinition with bulk data transformations for better results?

Question

Here is a situation for bulk loading in Oracle:

Schema A  { T1, T2, T3, T4}
Schema B  { TR1, TR2}

Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of data that need to be loaded into empty schema B. There is both data transformation and data mapping involved between schemas.

Would dbms_redefinition package help here? With my reading, it seems to work to redefine table within the same schema. Not sure if it works in the above situation. Also, how does this approach compare to others like CTAS.

Answer 1

John, normally it does not make sense to use dbms_redefinition in your case since you are not redefining a table. You are creating an extra schema. dbms_redefinition is meant for situations where for example your table T1 gets an other layout and this has to be done online. Application can stil use the old form of the table while you are completing the new table. This is done by creating materialized view logs.

I hope this helps.