I have not done much PL/SQL for a while, please be patient.
I am having problems getting the code below to compile. Simply, the goal is to get all indexes for a given schema and execute the ANALYZE INDEX command for each index.
When I try to execute the code to compile it, I get two errors:
- Error(10,7): PL/SQL: SQL Statement ignored
- Error(11,12): PL/SQL: ORA-00942: table or view does not exist
Can I kindly get assistance with this.
CREATE OR REPLACE PROCEDURE AST.SP_AnalyzeIdx
AS
SQL_STR VARCHAR2(1000);
BEGIN
DECLARE CURSOR C1 IS
SELECT OWNER, INDEX_NAME
FROM DBA_INDEXES WHERE OWNER = UPPER('AST');
v_Owner VARCHAR2(200);
v_IndexName VARCHAR2(200);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO v_Owner, v_IndexName;
EXIT WHEN c1%NOTFOUND;
SQL_STR := 'ANALYZE INDEX ' || v_Owner || '.' || v_IndexName || ' COMPUTE STATISTICS';
EXECUTE IMMEDIATE SQL_STR;
DBMS_OUTPUT.PUT_LINE('SUCCESS : ' || v_Owner ||'.' || v_IndexName);
END LOOP;
CLOSE C1;
END;
END;
/