Major Update to Rebuild Table Script

 Few months back:

I've shared a script to rebuild tables in Oracle using online rebuild options like [(DBMS_REDEFINITION (9i+) & ALTER TABLE MOVE ONLINE (12.2+)] if those features are not available in the database (i.e. database edition is Standard Edition) the script will utilize the legacy "ALTER TABLE MOVE" command along with rebuilding indexes.

The original post link: http://dba-tips.blogspot.com/2019/05/rebuild-table-script-and-reclaim-wasted.html

What is new?

I received many enhancements requests from readers along with bug fixing and here is a summary of new features:

Major Enhancements:

- When using ALTER TABLE MOVE ONLINE or the legacy one, you will be prompted to choose whether to keep the table in its original tablespace or you want to move the table to another tablespace:

Leaving it blank will rebuild the table on its original tablespace.

Of course, if you choose a different tablespace the script will automatically calculate the free space on the tablespace and make sure it's sufficient to host the table after the rebuild.

Note: This feature is not added to DBMS_REDEFINITION, although it's available starting with 12.2,  it will need the user to specify the tablespace for each index separately, I thought it will be better to not add this feature to not confuse the user, anyways the user can still use "ALTER TABLE MOVE ONLINE" option which is already available on 12.2 which will give similar result.

- Bug fixes along with reorganizing the steps to make it more simple for the user.

Minor Enhancements:

- User can enter blank value when asked for Parallelism Degree to utilize the maximum CPU power on server without the need to calculate it. "For those who are lazy to check CPU count on the machine 😊"

- Entering 0 value to Parallelism Degree will be accepted to disable the parallelism.

By the way, the info statement shown above "don't exceed the current number of CPUs" is my advice, of course you can exceed the number of CPUs and it may be faster for you, but in most cases I studied it won't help much, better you validate that yourself on your hardware!

- More notifications, messages added to the script to keep the user updated with what is going on.

- Added more tolerance for user's wrong inputs. 

- If the table selected for rebuild is being replicated by goldengate or streams, the user will get an info message that target table may miss some data during the table rebuild (this took me days of testing ad building case scenarios till I figured out this limitation!). By the way, this is not a script limitation, it's how the Oracle rebuild technology works internally so far.

- changed the color of important messages from green to yellow to give it more appearance.

 

To download the script:

https://www.dropbox.com/s/bmgbc0u76okokcs/rebuild_table.sh?dl=0

GitHub version is updated as well