0

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?

  • 4
    Remove the ; from inside the string constant – a_horse_with_no_name Feb 25 '19 at 21:11
  • For performance problems, the first thing to do is check the Plan. – Michael Kutz Feb 25 '19 at 21:12
  • @MichaelKutz After removing the ; it worked. Can you cite a document that I can read in order to get a better understanding of PL\SQL? I feel like there isn't rhyme or reason as to when a ; is needed and when it is not needed compared to T-SQL. – h24601 Feb 25 '19 at 21:44
  • 1
    @h24601 -- Outside of "The Fine Manual", I don't have any. docs.oracle.com/cd/B19306_01/appdev.102/b14261/toc.htm – Michael Kutz Feb 25 '19 at 22:08
  • The ";" is a language-specific terminator for the SQL statements. It is not part of the language proper. When SQL statements are sent to the database, they are sent without the terminator. Hence when you dynamically execute a SQL statement (using PL/SQL, JDBC, Python or any language), you must not include that terminator. – Albert Godfrind Feb 28 '19 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.