I have for in loop to rebuild indexes of certain tables that have changed primary key. Anyway to select only indexes of these tables from USER_OBJECTS
linking with TABLE_NAME
in USER_TABLES
, also to exclude any IOT table's index.
FOR r IN (SELECT OBJECT_NAME AS OBJ FORM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX') LOOP
l_sql := 'ALTER INDEX '||r.obj||' REBUILD'||'';
EXECUTE IMMEDIATE l_sql;
END LOOP;
Above code just simply rebuild all indexes in the schema (included IOT, hence hit error ORA-28650: Primary index on an IOT cannot be rebuilt
)
I wouldn't use user_objects
at all; why not go from user_indexes
, and join that to user_tables
?
select ui.index_name from user_indexes ui
join user_tables ut on ut.table_name = ui.table_name
where ut.iot_type is null
So your loop becomes:
FOR r IN (
select ui.index_name from user_indexes ui
join user_tables ut on ut.table_name = ui.table_name
where ut.iot_type is null
)
LOOP
l_sql := 'ALTER INDEX "'||r.index_name||'" REBUILD';
EXECUTE IMMEDIATE l_sql;
END LOOP;
You don't really need l_sql
, but it might be useful for debugging.
Of course, you need to question why you're rebuilding all the indexes in the first place...