What is the correct way to rebuild an index of a specific constraint, without knowing the index name, since it might different between environments.
In this case, prefer not knowing the index name (existing condition), and not renaming it first.
ALTER INDEX (select index_name from user_constraints where constraint_name = upper(‘constraint123’)) REBUILD;
I tried casting, string, as is… and getting the following error
ORA-00953: missing or invalid index name
ALTER INDEX (select index_name from user_constraints where constraint_name = ‘constraint123’) REBUILD
>Solution :
Any time you don’t know an object name (table name, column name, etc..) you have to use dynamic SQL, and that requires PL/SQL. Here’s how you would do it:
CREATE OR REPLACE PROCEDURE p_rebuild_constraint_index (in_constraint_name IN varchar2)
AS
var_index_name varchar2(128);
BEGIN
SELECT MAX(index_name)
INTO var_index_name
FROM user_constraints
WHERE constraint_name = in_constraint_name;
IF var_index_name IS NOT NULL
THEN
EXECUTE IMMEDIATE 'ALTER INDEX "'||var_index_name||'" REBUILD';
END IF;
END;
Then simply call it:
BEGIN p_rebuild_constraint_index('CONSTRAINT123'); END;