Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 23 December 2012

Rebuilding Table and Index using Toad

After analyzing table sometimes a DBA need to relocate a table to a different tablespace or rebuild it with different storage parameters. They might need to drop a column and reorder the column. Check my other post for the reason when we need of table and index rebuilding.
You must connect with the schema owning the table you want to rebuild.
Open Rebuild Table from the Menu: Database –> Optimize –> Rebuild Table

§         Change the option tab (default) setting or leave it as.
§         With the storage tab specify the new storage parameters. You can see from the below figure the old storage parameter is 64k and now we are specifying 1M. This tab also permits you parallel properties as well.
§         With the ‘Column’ tab you can drop and re-arrange the order of column. You can re-arrange the column either of two order ‘primary key’ or ‘alphabetical’.
§         With the ‘SQL’ tab you are able to generate the SQL statement for this setting.
Sometimes DBA may need to rebuild the indexes. They may need to relocate the index to a different tablespace or rebuild it with different storage parameters. Also in next subsequent example they need to locate and rebuild those indexes that have become unusable or corrupted and whose storage parameters are unbalanced.
You can do it either of single rebuild options screens or multiple screen rebuild option.
On the Table rebuild screen you can select Index option to rebuild that particular table index or you can open the multiple rebuild index screen from the menu: Database –> Optimize –> Rebuild Multiple Index Object
§         Load your entire index from the ‘load my index’ button.
§         Then check the index you want to rebuild
§         Set the new storage from the ‘storage clause adjustment option.
§         You can check threshold and performance option tab for the impact of rebuilding such as: sort area size, parallel and non parallel, logging and no logging,
§         You are also able to set the email notification for this changing.
§         The tablespace tab permits you to relocate the index or index partitions from one tablespace to another. You cam move them all or by their size.
§         You can create script for these rebuild recommended index by clicking on the button.
§         Finally by clicking on ‘rebuild recommended index’ button you can rebuild the selected index.


Post a Comment