Bundle 5.2 Is Now Available

DBA Bundle V5.2 is now available for download:
https://www.dropbox.com/s/k96rl0f4g39ukih/DBA_BUNDLE5.tar?dl=0

It comes with the following features:

- rebuild_table.sh has been totally re-developed to utilize ONLINE table rebuild features such as DBMS_REDEFINITION and ALTER TABLE MOVE ONLINE. Check this link for more details [https://dba-tips.blogspot.com/2019/05/rebuild-table-script-and-reclaim-wasted.html]

- Added the reporting of the "Top Fragmented Tables" in the daily health check report script dbdailychk.sh.

- Fixing bugs and enhancing the execution time of the following scripts:
dbdailychk.sh
dbalarm.sh
backup_ctrl_spf_AWR.sh
gather_stats.sh
db_locks.sh
active_sessions.sh

If you are new to the DBA BUNDLE the following link will give you a detailed idea:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

Rebuild Table Online Script And Claim Space In Oracle

Yet another script! This script can rebuild a table and its indexes easily and safely (with minimal downtime as possible) based on the available features on your current Oracle version/edition. Although I'm reluctant to share scripts that deal with data directly, I thought it would be helpful for DBAs if it's used wisely!

Before you start using this script, please read the full post carefully to understand how it works.

First and foremost, I'm sharing this script in the hope that it will be helpful for you without any warranty, you have to test the script yourself on a test environment before running it against production.

What this script for?

 

Using available table rebuild/move features in the database is difficult, risky and requires reading tons of documentations and much time in practicing before you feel confident to start utilizing them.

This script simplifies that process by checking the best available options to rebuild, move or defragment a table and its indexes within a minimal downtime by using features like DBMS_REDEFINITION (9i+) or ALTER TABLE MOVE ONLINE (12.2+), if non of these features are available in your database edition, the last resort will be utilizing the ALTER TABLE MOVE legacy feature.

 

How it works:

In a nutshell, the script can rebuild one table and its indexes at a time, once you enter the OWNER and TABLE_NAME it will check for the best available option to rebuild the table:

Option 1: It will check if DBMS_REDEFINITION package can be used based on the database edition (Standard/Enterprise) and then take the user through the rest of the steps. DBMS_REDEFINITION is a fantastic feature introduced since Oracle 9i and rarely utilized by DBA's, it can modify/rebuild a table with a very minimal downtime. [Real case of using DBMS_REDEFINITION: http://www.oracle.com/us/corporate/demantra-improve-performance-1900058.pdf]
 

Option 2: If DBMS_REDEFINITION is not available in the current edition or the user doesn't want to use DBMS_REDEFINITION, the script will move to ALTER TABLE MOVE option, if the database version is 12.2 or higher, the script will utilize "ALTER TABLE MOVE ONLINE" command new feature which will rebuild the table with a negligible downtime.

 

Option 3: If non of the above feature are available in your database (maybe because you are using a Standard Edition) the script will move you to "ALTER TABLE MOVE" command which will result in a complete downtime on the underlying table during the whole rebuild process.

The following flowchart will explain the mechanism of the script in details: [I'm grateful to draw.io for making the drawing of this flowchart in an easy way and free of charge]

If you are still confused, read the script prompted messages carefully and it will explain itself.

Here is the download link:
https://www.dropbox.com/s/bmgbc0u76okokcs/rebuild_table.sh?dl=0

In case the download link is broken you can copy the script from the below GitHub version: