The REBUILD_INDEXES stored procedure can be used to rebuild indexes in the INUSER schema. It’s not very often that indexes should require a rebuild but the following procedure helps facilitate the operations should it become necessary.
| Parameter | Description | |
|---|---|---|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The name of the schema owning the table and indexes. | |
| TABLE_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NULL | |
| Description | The table name for which to rebuild its indexes. | |
| INDEX_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NULL | |
| Description | The name of the index to rebuild. | |
| TABLESPACE | Type | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NULL | |
| Description | The name of the tablespace if moving the indexes to a new tablespace. | |
| MAX_MINUTES | Type | INPUT |
| Datatype: | NUMBER | |
| Default Value: | 0 | |
| Description |
The number of minutes to allow the procedure to start new index rebuilds. 0 = No time limit |
|
| PARALLEL | Type | INPUT |
| Datatype: | NUMBER | |
| Default Value: | 1 | |
| Description |
The degree of parallelism to use while rebuilding indexes 1 or More 1 = NOPARALLEL |
|
| LOGGING | Type | INPUT |
| Datatype: | VARCHAR2(9) | |
| Default Value: | LOGGING | |
| Description | Specifies if the index rebuild should be done with LOGGING or NOLOGGING. | |
Exceptions
Examples
SET SERVEROUTPUT ON;
-- Rebuild all indexes on the IN_WF_ITEM table with a degree of 4 for parallelism
EXEC IN_DB_UTIL.REBUILD_INDEXES ('INUSER', 'IN_WF_ITEM', NULL, NULL, 1, 4, 'LOGGING');
-- Rebuild only the WF_ITEM_IDX_05 index
EXEC IN_DB_UTIL.REBUILD_INDEXES ('INUSER', 'IN_WF_ITEM', 'WF_ITEM_IDX_05');
Example Output
SQL> EXEC IN_DB_UTIL.REBUILD_INDEXES ('INUSER', 'IN_WF_ITEM', 'WF_ITEM_IDX_05',NULL,0,4);
Procedure Name: REBUILD_INDEXES
Current DB Time: 2022-09-02 12:16:36.780746
Current UTC Time: 2022-09-02 17:16:36.780746
Current Activity for the IN_DB_UTIL identifier:
IN_DB_UTIL TAG: 06PLM0ZOVQ
IN_DB_UTIL STATUS: EXECUTING
IN_DB_UTIL OPERATION: REBUILD_INDEXES
IN_DB_UTIL CALLED BY: NULL
IN_DB_UTIL START TIME: 2022-09-02 12:16:36.731688
IN_DB_UTIL MAX TIME: NULL
IN_DB_UTIL MAX MINUTES: NULL
IN_DB_UTIL OBJECT: NULL
IN_DB_UTIL SUBOBJECT: NULL
IN_DB_UTIL TABLESPACE: NULL
IN_DB_UTIL BATCH SIZE: NULL
IN_DB_UTIL BULK LIMIT: NULL
IN_DB_UTIL PARALLEL: 4
IN_DB_UTIL LOGGING: LOGGING
IN_DB_UTIL OS USER: OSUSER
IN_DB_UTIL DB_SESSION: 126,65152
Using the default value of 0 for MAX_MINUTES.
Indexes will be rebuilt until all qualifying indexes have been rebuilt.
***********************************
*** Index Rebuilds ***
***********************************
----- Index Details -----
Table Name: IN_WF_ITEM
Index Name: WF_ITEM_IDX_05
Tablespace Name: NEWINDX
Status: VALID
Last Analyzed: 2022-09-02 12:16:20
Sample Size: 8,568,289
Number of Rows: 8,568,289
ALTER INDEX INUSER.WF_ITEM_IDX_05 REBUILD ONLINE LOGGING;
Duration: 6.486054 Seconds
Rebuilt the INUSER.WF_ITEM_IDX_05 index.
***********************************
*** Index Rebuild Results ***
***********************************
Successfully rebuilt 1 index
Total Duration: 6.538909 Seconds
The procedure completed successfully.
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.63