ORA-00911 invalid character

Question

Anyone help me with this error

declare  
  cursor cur1 is  
    select owner,table_name from dba_tables;  
  c1 cur1%rowtype;  
  str varchar2(1000);  
begin  
  for c1 in cur1 loop  
    str:='analyze table '||c1.owner||'.'||c1.table_name||' list chained rows';  
    execute immediate str;  
  end loop;  
end;  
/

declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9

Maybe this error is caused by some table names. Have you tried to add a condition to your cursor, something like this: " where table_name = 'KNOWN_TABLE_NAME'"? — Multisync
yes procedure completes successfully but statistics is not generated — Ganesh

Alex Poole Alex Poole · Accepted Answer · 2014-10-13T15:46:07

You appear to have a table (or user/schema) identifier which does not conform to the usual pattern and the database object naming rules, and which must therefore have been created as a quoted identifier.

For example, a table name starting with a number, or with a space in the name - you'd need to check your data dictionary to find out exactly what it's tripping over, or add a debug before the execute immediate to do dbms_output.put_line(str);.

Whatever it is though, you can just quote the identifier in the analyze command too:

    str:='analyze table "'||c1.owner||'"."'||c1.table_name||'" list chained rows';

So if your current query generates a command like:

analyze table MYSCHEMA.42 list chained rows

... where the 42 is an invalid identifier, the modified version would do:

analyze table "MYSCHEMA"."42" list chained rows

This will also take care of any mixed-case names, which would also cause the analyze to fail unless quoted.

This is why quoted identifiers are not recommended by Oracle and are usually disliked by anyone who has been forced to deal with them. The identifier has to be quoted whenever it is referenced anywhere, and be used with exactly the same case as when it was created. If you have a choice, don't use them.

It may not be your fault though. On my 11gR2 instance there's an entry in dba_tables for "SYS"."_default_auditing_options_" which breaks the naming rules by starting with an underscore, and was created quoted in lowercase. (And that seems to have been there since at least 8.0). Not sure manually analyzing SYS tables is necessarily a good idea anyway, it might be better to restrict this to your own schema(s).

ORA-00911 invalid character

1 Answers