Oracle rebuild the index of all tables (rebuild index)

CREATE OR REPLACE PROCEDURE p_rebuild_all_index (tablespace_name in VARCHAR2,-this is the table space name, and you can pass in NULL if you do not change the table space
Only_unusable in BOOLEAN)--is only an invalid index operation
As
SQLT VARCHAR (200);
BEGIN
--only non-temporary indexes
For IDX in (SELECT index_name, Tablespace_name, STATUS
From User_indexes
WHERE temporary = ' N ') LOOP
-If an invalid index is rebuilt, and if the index is not invalid, skip
IF only_unusable = TRUE and IDX. STATUS <> ' unusable ' THEN
GOTO CONTINUE;
End IF;

IF (Tablespace_name is NULL) OR IDX. STATUS = ' unusable ' THEN
--If no tablespace is specified, or if the index is not valid, rebuild in the original table space
SQLT: = ' ALTER INDEX ' | | IDX. index_name | | ' REBUILD ';
elsif UPPER (tablespace_name) <> IDX. Tablespace_name THEN
--If a different table space is specified, the index is to be built in the specified table space
SQLT: = ' ALTER INDEX ' | | IDX. index_name | | ' REBUILD tablespace ' | |
Tablespace_name;
ELSE
--If the table space is the same, skip
GOTO CONTINUE;
End IF;

Dbms_output. Put_Line (IDX. INDEX_NAME);
EXECUTE IMMEDIATE sqlt;
<<CONTINUE>>
NULL;
End LOOP;
End;
/*
Function: Rebuilds the index.
Note: If the table space parameter passes in NULL, the index is rebuilt in the original table space, otherwise the index is rebuilt in the destination table space.
Skips if the table space is the same.
Only_unusable indicates whether to rebuild only invalid indexes
Author: 81, June 26, 2007
*/