Huge Data Migration in Oracle and space concerns

Question

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??

Answer 1

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.

Answer 2

You can calculate the end size before hand. A lot easier is to test with a sample. Just deleting rows from your source table won't solve your space issue. The table uses a certain space and it will remain doing so until you are able to empty complete blocks from the end of the table and can adjust the high water mark. If your source table is partitioned, easiest is to drop the empty partitions. This will free space that can be re-used by your table2. This only works if you commit after emptying a partition, otherwise, the partition will be locked. Fastest will be working by partition and drop them when copied. That is faster than deleting the rows.

Answer 3

If temp space is cheap (and it often is) you can use it for an intermediate stage, for example:

create table t1 as select level as id from dual connect by level < 10;
create global temporary table gtt on commit preserve rows as select * from t1;
drop table t1;
create table t2 as select id, id+1 as id2 from gt;
drop table gt;

BUT if your connection fails after drop table t1 and before create table t2 finishes, then you will lose the data. As you have said "Assume that user would have already backed up the source data from schema A", that should be ok in your case.