Skip to Content

Rebuilding Partitioned Indexes in Oracle

I wrote a blog earlier about [Rebuilding Indexes Online using DB02 | Rebuilding Index online using DB02] , in the blog I had mentioned that there is no option to rebuild partitioned indexes. So, I thought i’ll follow that blog with this one wherein there are steps of rebuilding a partitioned index.

Let us revisit the unbalanced index problem that was mentioned in the blog mentioned above.

Problem – Unbalanced Partitioned Index

There might me some warnings such as:

Unbalanced Partitioned Indexes

These are partitioned indexes, they cannot be rebuilt using DB02 or using RSANAORA report.

The following is what we need to rebuild partitioned indexes.

The Solution – Using SQLPLUS

Prequisites/Assumptions: Start SQLPLUS:<br /><br />We login as ora<sid> into the system and start sqlplus by using a command:<br /><br />sqlplus “/ as sysdba”

You should see a similar output

Login into sqlplus


List Index Partitions:

Now that we are logged into sqlplus, we list the partitions of the affected index. To list the partitions we query the table dba_ind_partitions with the command as follows:

select index_name, partition_name from dba_ind_partitions where index_name = ‘<affected_index_name>’;

As shown below, we will get the list of partitions:

List of Partitions

Now as show above we have the partition_name , copy this name, however before we reubild the index partition, BW systems can have huge index partitions.


Determine the Size:

This can be easily determined from SAPGUI, login into the SAP system and start DB02, now click on the Detailed Analysis button to bring up the following screen:

Detailed analysis

Here we provide the partition name, we may have to provide *asterisk as shown above, now click the green tick and you should get the following screen:

Index partition size

If the index partition is bigger in size, you may want to perform the activity on a non-working day or a quiet period.


Rebuild Partition:

Now that we have determined its okay to rebuild the partition, we actually perform the rebuild using the following command:

alter index <index_name> rebuild partition <partition_name>;

The command will execute as shown below:

!|height=101|alt=Rebuild the partition|width=568|src=|border=0!</body>

Be the first to leave a comment
You must be Logged on to comment or reply to a post.