Step By Step Blueprint For Oracle Table Reorg – Index Rebuild

I am so excited to share with you the blueprint, or the plan that you can use to implement the maintenance process on your database.
If you were daunted by this task before, afraid to proceed, because you didn’t even know where to start, then this article is for you.
If you had an idea on what needs to be done, but didn’t actually put a plan together, then this article is for you.
If you already have a plan, and want to verify you covered all the steps, then this article is for you.

I am showing you step by step the exact process I use, to reorganize tables and rebuild indexes in an Oracle database.
I am taking the guesswork out of the process. You can customize this plan to your needs, as I am sure, there could be steps that don’t apply to your database.

This is the last part of the 3 part series for Oracle Database Maintenance. Reorganize Tables And Rebuild Indexes, The Oil Change For Your Database, discussed the importance of reorganizing tables, and rebuilding indexes, why and when you would do it.
7 Things To Consider When Reorganizing Tables And Rebuilding Indexes, reviewed the main challenges that can rise during the reorg process, such as: partitioned tables, IOTs, tables with LONG and/or LOB data types, indexes.

Today, I am putting it all together, and share with you, the Step By Step Deployment Plan, for reorganizing tables and rebuilding indexes. You will need to create your own scripts, and that part is easy, once you know what scripts to create.

When you have a plan, or a “blueprint” for a particular task, the task becomes easy, it is not daunting anymore. What challenges will you encounter? I don’t know and you don’t know it either, until you actually do it.

Enjoy my friend! If you have any questions or feedback, feel free to leave a comment in the comments section, or email me!

Step1 – Stop Application

Perform any steps that you normally take when an application outage occurs, such as:

• stopping application
• migrating application to a different data centre
• stopoing replication

The bottom line is, you don’t want users, applications, and other process touching the tables that you are working on.

Step2 – Login To The Database

It is critical that you are in the right environment when you proceed. At times it can be confusing as to where you are running the scripts, especially when you have multiple windows open. My preference is to run the scripts right on the database server.

• login to the server
• set your environment to the desired database
• login to the database with a high privileged account
• verify database name, to confirm you are in the right environment

Step3 – Run PRE-Checks

Capture the following information with the pre_checks.sql script, and spool to a log file:

• tablespace size (of the tablespace you are performing reorg in)
• table size (of the tables that will be reorganized)
• index size and status (of the indexes that will be rebuilt)
• invalid objects in the database (compile them prior to start, and get a list of the ones that won’t compile)

Step4 – Generate Reorg Scripts

This is the step where you think about everything that could reside in the tablespace that you are reorganizing, or any objects that you are working on. This is where the scope of this deployment plan can be increased or decreased. Each script generates a RUN script, that will be run in a Step7.

• gen_table_reorg.sql (generates regular table reorg statements)
• gen_table_part_reorg.sql (generates partitioned tables reorg statements)
• gen_IOT_reorg.sql (generates index organized table reorg statements)
• gen_LONG_col_reorg.sql (generates export datapump parameter file, drop statements, import datapump parameter file, for tables with LONG columns)
• gen_LOB_col_reorg.sql (generates reorg statements for tables with LOB columns
• gen_index_rebuild.sql (generates index rebuild statements)
• gen_gather_stats.sql (generates the gather statistics statements for each object that was reorganized or rebuilt)

Step5 – Rename Tablespaces To _OLD

If you want to keep the same tablespace names for tables and indexes, and still move the objects out to a brand new tablespace, then you need to rename the existing tablespace(s)
I usually prefer using the naming convention of tablespace_name_OLD, this way it is easy to spot it at a later time.
If you don’t really care about tablespace names, and you just start with new names, then this step can be ignored.

• rename_tbs_old.sql

Step6 – Create New Tablespaces

You need to create the tablespaces where the tables will be moved to, and indexes rebuilt.

• create_tbs_new.sql

Step7 – Run the maintenance scripts

This is the step where you are actually doing the work. Everything up until now, lead to this step. You will run all the run_* scripts generated in Step4.

• run_table_reorg.sql
• run_table_part_reorg.sql
• run_IOT_reorg.sql
• run_LONG_col_reorg.sql (this step would include the datapump export, drop, datapump import)
• run_LOB_col_reorg.sql
• run_index_rebuild.sql
• run_gather_stats.sql

Step8 – Verify Index Status

You need to confirm that all indexe that were usable, are still in a usable state, and that no index rebuilds got missed.

• verify_index_status.sql

Step9 – Verify No Objects In “_OLD” Tablespace and Drop “_OLD” Tablespace

If you decided in Step5 to rename the original tablespace to _OLD, now is the time to confirm no objects reside there.
If your check is positive, and no objects exist in the tablespace, it is safe to drop it.

• verify_no_obj_old_tbs.sql
• drop_old_tbs.sql

Step10 – Compile Invalid Objects

The database maintenance could invalidate some objects, such as synonyms, views, procedures. These objects need to be compiled. You need to confirm that all the objects that were valid prior to your deployment, are still valid after.

• gen_compile_invalid_obj.sql
• run_compile.sql

Step11 – Alter Default Tablespaces For Users

When you rename the tablespace to _OLD, the default tablespace for users, also gets renamed to _OLD. Users’ default tablespace needs to be altered from _OLD to the new tablespace.

• alter_user_default_tbs.sql

Step12 – Run POST-checks

Capture the following information with the post_checks.sql script, and spool to a log file:

• tablespace size (of the tablespace you were performing reorg in)
• table size (of the tables that were reorganized)
• index size and status (of the indexes that were rebuilt)
• invalid objects in the database

The output file can be compared with the log file generated in Step3.
Now you can create some nice reports for management. You can create a comparison between tablespace sizes, table and index sizes before and after the reorg process. Management likes to see reports, and numbers. You can show them that you reduced the database size by X%.

Step13 – Backup Your Database

You created new tablespaces and dropped old ones. Take a backup of your database!

• backup_database.sh

You Did It, you got to the end of the deployment, and your database maintenance has completed!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

-Diana