Basically my thought was, In a normal data load scenario where we have to
move TB's of data(say 1TB+), considering post data load indexing will take
the same amount of time in any case. Whether it's suggested to go for
INSERT APPEND method of data load in chunks OR doing one shot data load
using CTAS? In both the cases there will be almost zero UNDO i, so i was
wondering if any limit is there for one shot dataload, so that we should
not go for CTAS approach after a certain size of data but have to do that
in chunks only(may be using INSERT.. APPEND method only)?
Regards
Lok
On Thu, Apr 1, 2021 at 6:49 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Thanks much, Jonathan. It worked after shrinking the top segments. Thanks
again.
Out of curiosity , and as we were almost end up deciding to attempt this
one time load, so considering ~18billion rows( worth ~1TB+ size ) to be
moved at one shot , whether it's sensible to go for "Insert append into
stage table ... select.. from source table" approach OR "CTAS stage
table.. select from source table"? I believe in both cases UNDO will be
almost Zero as no index is there in them, so wondering what other DB
resource it will consume?
On Thu, Apr 1, 2021 at 6:19 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Thank you so much Jonathan. As you rightly mentioned , the Shrink of
specific rollback segment seems to be the best work around to get away in
this situation. i'm going to try it now. Thanks.
On Thu, Apr 1, 2021 at 5:07 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Follow-up to my previous comments about dropping or affecting the size
of undo segments. Read MOS nod 1580182.1
The basic comment is that you can hit this problem if you have very long
running transactions and a very large undo retention - and it probably
needs a reasonable level of concurrency as well, but that's not stressed.
It is possible to set a debug parameter that allows you to shrink undo
segments by name - details in the note.
It seems to work.
Regards
Jonathan Lewis