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.

  • Hi @John ~ I'm not entirely sure what it is you're asking. Are you asking if dbms_redefinition is the right tool for this job?
    – jcolebrand
    Mar 22 '11 at 5:03

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.

  • I am in fact redefining the table through refactoring here but just that this refactored table is created in a new schema.
    – John
    Mar 25 '11 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.