Why Rebuild Index | Step by Step to Rebuild Index in Oracle Online

Why to Rebuild Index in Database?

Rebuilding index is the common task we do in database maintenance activities. But lot of people still have a question in mind, what is rebuild and why we rebuild index in Database. Let me explain today the reason for rebuild.

Database automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. 

When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

Step by Step process of How to rebuild Index in Oracle Online.

Index rebuilding can be easily done using the brtools in Oracle. here are the steps we follow to rebuild index in oracle online using brtools.

Step 1 : Login to server thru OS Level using User orasid
Step 2:  Enter command brtools to perform the rebuild index in oracle online
Step 3 : As shown in the screenshot below select enter option 3 - Segment Management from main menu of Brtools

type 3 in the Enter your choice

Step 4 : In the next screen as shown below choose option 2 Rebuild Indexes to Rebuild index in Oracle.

Step 5 : In the Next screen choose option 3 to enter the Index names (index) to fill in the index name. Then, choose option 3 ~ Rebuild action (action)

Step 6: similarly choose 4 and 7 in the next steps to update the table name and index name accordingly to rebuild index in oracle online

Step 7 : after updating the necessary information like Table name, Index name as shown below

Step 8 : Choose c (twice) to execute the Index rebuild process online, in the process if you recieved any warning related to Auto extension of table space, choose c and continue.

Step 9 : When the Online Index rebuild in Oracle started we can see the status as shown below

Thanks and Regards,
Maina.in

Read More

Flashback point in Oracle and commands to create Drop the Flashback Point in Oracle

This post explains you in details what is a Flashback or Restore point in Oracle and Commands to create the Flashback points and command to drop the flashback point.

What is a Flashback Point?

Flashback and restore points in Oracle database are data protection related features that permit you to wind back data in time to correct any teething troubles affected by logical data corruption or user errors within a designated time window. These features deliver a more capable alternative to point-in-time recovery and does not require a backup of the database to be restored first. 

The effects are similar to database point-in-time recovery (DBPITR). Flashback Database and restore points are not only effective in traditional database recovery situations but can also be useful during database upgrades, application changes deployments and application testing scenarios when test databases must be quickly created and re-created. Flashback Database also provides an efficient alternative to rebuilding a failed primary database after a Data Guard failover.

You can utilize flashback feature to view past conditions of information and rewind parts or the greater part of your database. When all is said in done, flashback feature are more effective and less troublesome than media recovery much of the time in which they apply.

Commands to Create Flashback restore point in Oracle.

1. Login to Oracle using orasid.
2. Go to Sqlplus
3. Enter the below command.

create restore point <Flashback point name> guarantee flashback database;

Flashback point name can be anything and is for reference eg: change number or change date.

Command to check if flashback point is created.

Select * from v$restore_point


Commands to Drop Flashback restore point in Oracle.

Drop restore point <Flashback point name>;

Command to check if flashback point is dropped successfully.

Select * from v$restore_point;

Read More