Here is a function that rebuilds or coalesces the indexes of a specified user.If asked to rebuild the indexes then you can supply a table space to move the rebuild indexes into.
The function takes three parameters :
- user_name : The name of the user whose indexes are to be updated
- rebuild_type : Ether C or R to indicate Coalesce or Rebuild operation
- tablespace_name : Optional parameter to indicate where to move the indexes in case of rebuild
The function should be executed by a user with DBA privileges and this code is as follows
create or replace PROCEDURE rebuild_user_indexes( user_name IN VARCHAR2,
rebuild_type IN VARCHAR2,
tablespace_name IN VARCHAR2 DEFAULT NULL)
AS
full_index_name VARCHAR2(100);
index_command VARCHAR2(10);
sql_str VARCHAR2(100);
total_indexes Integer;
updated_indexes Integer;
CURSOR get_index_cursor IS
SELECT index_name, index_type
FROM dba_indexes
WHERE owner = user_name AND
index_type in (
'BITMAP',
'NORMAL',
'FUNCTION-BASED NORMAL',
'FUNCTION-BASED DOMAIN');
BEGIN
/* check initial parameters */
IF rebuild_type <> 'R' AND rebuild_type <> 'C' THEN
raise_application_error( -20999, 'rebuild type should be "C" or "R"');
ELSIF rebuild_type = 'R' THEN
index_command := ' Rebuild';
/* if a rebuild tablespace is also defined */
IF tablespace_name <> '' THEN
index_command := index_command || ' tablespace ' || tablespace_name;
END IF;
ELSE
index_command := ' Coalesce';
END IF;
total_indexes := 0;
updated_indexes := 0;
/* get all indexes that belong to the specified user */
FOR r IN get_index_cursor LOOP
total_indexes := total_indexes + 1;
dbms_output.put_line( total_indexes || ' ' ||
r.index_name || ' ' ||
r.index_type);
/* Coalescing works only on normal B-Tree indexes,
* while rebuilding works with everything.
*/
IF (rebuild_type = 'R') OR
(rebuild_type = 'C' AND instr( r.index_type, 'NORMAL') > 0) THEN
full_index_name := user_name || '.' || r.index_name;
sql_str := 'alter index ' || full_index_name || index_command;
BEGIN
/* attempt to modify the index */
EXECUTE IMMEDIATE sql_str;
updated_indexes := updated_indexes + 1;
EXCEPTION
WHEN OTHERS THEN
/* display the command and the error that occured during index
* update
*/
dbms_output.put_line( sql_str);
dbms_output.put_line( sqlcode || ' ' || sqlerrm);
/* continue working */
NULL;
END;
END IF;
END LOOP;
/* report results */
dbms_output.put_line( 'Indexes examined :' || total_indexes);
dbms_output.put_line( 'Indexes Updated :' || updated_indexes);
END rebuild_user_indexes;
Thanks to SQLDeveloper running the function can is as easy as copying and pasting the following in SQLPlus.
DECLARE
USER_NAME VARCHAR2(200);
REBUILD_TYPE VARCHAR2(200);
TABLESPACE_NAME VARCHAR2(200);
BEGIN
USER_NAME := NULL; -- add your user here, for example 'HR'
REBUILD_TYPE := NULL; -- either 'C' or 'R'
TABLESPACE_NAME := NULL; -- optional new tablespace name if desired
REBUILD_USER_INDEXES(
USER_NAME => USER_NAME,
REBUILD_TYPE => REBUILD_TYPE,
TABLESPACE_NAME => TABLESPACE_NAME
);
END;
Note
The above code provides a very simplistic approach to index rebuilding as it rebuilds all indexes of a user. In order to determine the indexes in your database that would really benefit from rebuilding you need to run the script available from Oracle Metalink Note:122008.1.
