I am trying to do Insert append as part of data migration from SchemaA.table1 to SchemaB.table2 in the same Oracle database. Due to refactoring of Table2, there will be some form of data transformation occurring as part of this migration. i.e. I can't just do some kind of partition exchange here.
Due to the huge size of data, this may take anywhere from 10 hours to 30 hours. I don't want to fail the migration at the very end because of insufficient tablespace. Is there a way in Oracle to predetermine the space requirements based on the size of the data in table1 so that I can fail right way asking the user to increase the table space?
Alternatively, is there an efficient way to remove the data from table1 as I move them to table2 ? Assume that user would have already backed up the source data from schema A. This way, I guess I don't need to worry about space issues??
Best Answer
No, you can't pre-fail the process unless it's something you write yourself. However, you can use Oracle's Resumable Space Allocation to allow your session(s) to suspend for a given amount of time if it runs out of space, extents, or quota. Then once the space problem's been resolved, the transaction/session automagically keeps working from where it was suspended.