Folks/Gurus,
There is a existing table T1. In T1 a new column C1 is added. Table T1 is compressed, partitioned and contain around 13billion rows. Now, since table T1 is compressed and partitioned, as a result I used exchange partition technique in order to populate column C1. I created a temp table, looped the main T1 table partition by partition. Selected data partition wise from T1, inserted into temp table, populated column C1 in temp table and then finally did alter table exchange partition and truncated temp table in that loop. Everything is fine till here. Column C1 is correctly populated in table T1.
Now, I have been asked not to do partition exchange until the business have verified data on temp table. So, ideally what they are asking is, all 13billion rows in temp table to be verified and once they are satisfied with the data on temp table then only they want to initiate exchange partition. Also, the table T1 is heavily used for reporting purposes as a result business is concerned that during partition exchange it might cause some data locking or if any thing gets failed in between that might cause issues in reporting side. Is it true? Does locking happens when exchange partition takes place?
As per my understanding, exchange partition only works between a partitioned table AND a normal table. So, it means I cannot partition the temp table. Even if I am able to do so, I do not know how can I exchange between two partitioned tables.
Any idea/possibility where in I can exchange partition only after the data has loaded completely into the temp table? I know it will overrule the basic advantage of exchange partition but any suggestion how to achieve the same would be really appreciable.
Many Thanks