SQL> show user
USER is "OPS$NZLDOM01\BROCASK"
SQL> BEGINThis is a known issue that according to Metalink / MOS note
2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
3 OwnName => 'OPS$NZLDOM01\BROCASK'
4 ,TabName => 'PARENT_TAB'
5 ,Estimate_Percent => 0
6 ,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO'
7 ,Degree => 4
8 ,Cascade => FALSE
9 ,No_Invalidate => FALSE);
10 END;
11 /
BEGIN
*
ERROR at line 1:
ORA-20001: OPS$NZLDOM01\BROCASK is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 20392
ORA-06512: at "SYS.DBMS_STATS", line 20415
ORA-06512: at line 2
Bug 7229351 - DBMS_STATS ORA-20001 for username with special characters [ID 7229351.8]
Description
DBMS_STATS procedures may report ORA-20001 errors if a username contains a special character such as a '\'.
eg: Executing DBMS_STATS.DELETE_DICTIONARY_STATS as user OPS$FOO\BAR raises Error ORA-20001: OPS$FOO\BAR is an invalid identifier.
It is supposed to be fixed in
however I'm running 11.2 Patch 7 on Windows and the problem still exists :)
The note suggests as a workaround: "Do not use a user name that contains special characters"
Which is a bit hopeless given that using a domain user on windowes forces the username to be ...domain name\username.
However I was able to use Toad to collect stats, which made me wonder how it did it given that it was using DBMS_STATS. What it does is it whacks a pair of double-quotes around the username before it puts on the single quotes:
SQL> BEGINYay!
2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
3 OwnName => '"OPS$NZLDOM01\BROCASK"'
4 ,TabName => 'PARENT_TAB'
5 ,Estimate_Percent => 0
6 ,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO'
7 ,Degree => 4
8 ,Cascade => FALSE
9 ,No_Invalidate => FALSE);
10 END;
11 /
PL/SQL procedure successfully completed.
