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.
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]
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:
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:
Amazing script but it misses to send the DBA an email confirmation when the rebuild complete.
ReplyDeleteActually it will write a message to the alertlog starting with ORA-NOTIFICATION... indicating the completion of the rebuild operation or requesting the DBA to complete the final step of the rebuild by running specific command manually, in case you are monitoring your alert log for "ORA-" errors via a script or a tool, it will catch that message and you will get informed. This is why I started this notification message with "ORA-".
ReplyDeleteVery good script, thank you for your work.
ReplyDeleteThe script works fine but I'm getting this error:
ReplyDelete[Calculating Table Size] ...
awk: BEGIN {print 88 + 12:20:04 + 6}
awk: ^ syntax error
[TABLE & INDEXES SIZE: MB]
awk: BEGIN {print * 1.25}
awk: ^ syntax error
The rest of script is fine.
I can see the value of indexes size was translated to "12:20:04" which caused this error, I'm not sure how this value get picked up!
ReplyDeleteI've received your Email as well and replied you with specific lines to add to the script it will help me understand how the indexes size got that wrong value, please run the script one more time after the modification and share the output with me.
After investigation, this problem occurs whenever login.sql is exist on the current working directory. I've fixed it in the script version 2.5 and just uploaded it.
ReplyDeletehello,
ReplyDeletewhile I'm rebuilding one table using your script it showed the new rebuilded table with bigger size than the original fragmented table, how this happen?
Running a COMPARISON between the OLD and NEW TABLE:
***************************************************
TABLES:
-----
Table Name TABLESPACE_NAME EXTENTS COMPRESS READONLY SIZE_MB
----------------------------------- -------------------- ---------- -------- -------- ---------- ORIGINAL_TABLE: ZX_LINES_DET_FACTORS APPS_TS_TX_DATA 84474 DISABLED NO 10549
NEW_TABLE: ZX_LINES_DET_FACTORS_XNT APPS_TS_TX_DATA 88905 DISABLED NO 11102
ORIGINAL_TABLE NUMBER OF ROWS: 12045445
NEW_TABLE NUMBER OF ROWS: 12045445
Sorry, outputs scrambled:
ReplyDeleteTable Name SIZE_MB
ORIGINAL_TABLE: ZX_LINES_DET_FACTORS 10549
NEW_TABLE: ZX_LINES_DET_FACTORS_XNT 11102
Thanks for the clear description.
ReplyDeleteActually, most of the time fragmented tables will not show their accurate size, it's something wrong with the data which DBA_SEGMENTS fetches, and by the way I'm not concerned here about the table size before the rebuild as I'm concerned about how much of MBs got freed up on the tablespace hosting that table after the rebuild and after dropping the interim table, if you compare the size of that tablespace before and after rebuilding the table, you will find a big space gain after dropping the old fragmented table: SQL> drop table .ZX_LINES_DET_FACTORS_XNT purge;
Please let me know how much space got freed up on the hosting tablespace after the rebuild.
yes you were right, comparing the tablespace size before and after I can see more than 60Gig of space been claimed after dropping the interim table.
ReplyDeleteThanks to you for the help.
Most welcome.
ReplyDeleteGood to know that you gained that much of space, looks indexes rebuild after the actual table rebuild has contributed much to this space as well.
Thanks, I was looking for such a script very long time. I did it always manually, step by step. Are you planning to add rebuild to another tablespace ? Thanks again
ReplyDeleteHi Darko,
ReplyDeleteFor rebuilding the table to another tablespace this feature is added in the coming release for both "ALTER TABLE MOVE" and "ALTER TABLE MOVE ONLINE" options; I'll publish it shortly. But for DBMS_REDEFINITION this feature is not available in DB versions older than 12c, for 12c the steps has changed entirely and this needs a radical change in the script, if I find time I'll include this feature for 12c.
But if your DB is 12c or higher I would recommend you to go with "ALTER TABLE REBUILD ONLINE" feature which will rebuild the table with minimal downtime as well.
While the script accept using dbms_redefinition it moves to 'alter table move' option
ReplyDeleteDo you want to use DBMS_REDEFINITION for ONLINE Table REBUILD with a MINIMAL DOWNTIME on the table? [Y|N] Y
===================================================================================================
Enter NO in case you want to use ALTER TABLE MOVE option.
INFO: Current PCTFREE: 10
INFO: Number of "UPDATE" transactions on the table since the last Statistics Gather on []: 0
Specify the table PCTFREE after the rebuild: [How much %Free space will be left in each block for future updates | Current 10%]
===========================================
Note: The SMALLER the PCTFREE the SMALLER the table size after the rebuild. [Recommended for Archival/Datawarehouse Tables]
Note: If the table is highly updated it's recommended to keep the current PCTFREE: 10
Leave it BLANK and hit Enter to keep the default PCTFREE.
Moving to "ALTER TABLE MOVE" Option.
[Table [ra.GG_TEST_TAB2] Contains UN-SUPPORTED "LONG" DATA TYPE for "ALTER TABLE MOVE" operation.]
Script Terminated!
what is wrong?
Your table has one or more columns with LONG data type. None of the rebuild options will be able to rebuild that table unless you change the data type from LONG to CLOB
ReplyDeletee.g.
alter table ra.GG_TEST_TAB2 modify column1 CLOB;