I would like to execute the analyze index command for every user index within a database in order to populate the index_stats view in order to detect fragmented indexes by (DEL_LF_ROWS/LF_ROWS).
The version of Oracle that the client is using is 10.2.0.3. This has made it even more challenging to find valid examples. I primarily work with the MSSQL database platform but the client has multiple queries that use Linked Server connections to the Oracle databases therefore the Oracle database performance problem has become my problem.
The code that I am working on is below.
DECLARE v_variable_tx VARCHAR2(32000); BEGIN for item in (select owner, index_name from DBA_IND_STATISTICS where owner not in ('SYS','SYSTEM','DBSNMP')) LOOP v_variable_tx:= 'ANALYZE INDEX ' || item.owner || '.' || item.index_name || ' VALIDATE STRUCTURE OFFLINE;'; --execute immediate v_variable_tx; DBMS_OUTPUT.PUT_LINE(v_variable_tx); END LOOP; END;
The error that I am getting when executing the above loop is...
RA-00911: invalid character ORA-06512: at line 8 00911. 00000 - "invalid character"
When I run the put_line text the command executes successfully and I can see the index stats in the view. Why does it fail when it is executed using execute immediate?