Thursday, October 28, 2010

DBMS_STATS returns ORA-20001 is an invalid identifier for externally authenticated Windows domain user names

Trying to collect stats as an externally identified Windows domain user on a wee table in a wee database on my notebook I got:
SQL> show user
USER is "OPS$NZLDOM01\BROCASK"
SQL> BEGIN
  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
This is a known issue that according to Metalink / MOS note
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> BEGIN
  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.
Yay!

3 comments:

  1. Palmer was signed to a development deal with the Web site. He will produce at least one other short film, probably another spoof, for Netcast. plagiarism finder

    ReplyDelete
  2. Great Submit. Thank you regarding revealing. Yet I am going to discuss any tips. Most of us need to get Yahoo leading rating with your content/posts. Yet several moment we all can't fully grasp this. Have you any idea exactly why? Yahoo need Refreshing and also Unqiue articles. Yet tips on how to comprehend my own articles will be totally free and also Unqiue. Because of this utilize Plagiarism Detector equipment. Yet many don’t utilize Yahoo. Here is the initial application My goal is to discuss that may utilize Yahoo to have offer your articles Special or perhaps not necessarily. The particular huge portion will be that equipment will be totally free. Thus do that totally free Plagiarism Software and acquire initial page ranking effortlessly.

    ReplyDelete
  3. Free plagiarism checker for avoiding plagiarism, try best anti plagiarism detection tool for detecting plagiarism online.

    ReplyDelete