Oracle 11.2.0.2 Std. Ed One
ASM 11.2.0.3
Oracle Linux 5.6 64bit
This is a follow on to my previous thread "deleting data file that has been offline for extended time". Also posted on OTN
Long story short, due to a perfect storm of errors by my back-fills while I was out on medical leave, I am having to move all segments from one TS to a newly created TS and drop the old when it is empty.
I have a script found on AskTom to generate all the necessary ALTER TABLE MOVE and ALTER INDEX REBUILD statements. For sanity, instead of running them all en mass, I’m taking each table in turn (and good thing I am as I am finding a few issues with the generated script as I go) working from the smallest to the largest table. I only have about a dozen tables left to do as I’m getting to the biggest ones.
So far the biggest table I’ve moved is 1.5 GB and it took about 95 seconds to run. I have a few tables of over 11GB, with the largest being 48GB. (as measured by the BYTES column in dba_segments). Based on the work so far, I’m estimating the largest table will take about an hour. With that I’m starting to develop some concerns about any risks involved.
Specifically
1 – Impact on redo.
This isn’t a huge concern. We are in log mode, so I’d expect a possible uptick in archive generation, but as long as the destination has room, no real problem, unless I'm overlooking something.
2 – Impact on rollback.
???
3 – Fallout from any failures …. fear of unknown unknowns.