Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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.

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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