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!

Monday, October 25, 2010

Install 11g Release 2 RAC On Linux Using VirtualBox Walkthrough

Tim Hall has provided a nice walkthrough of installing 11.2 RAC on VirtualBox using shared disk rather than a 3rd software NAS.

Because 11.2 clusterware is now a bloated fat pig and uses atleast 1 GB of RAM per machine, to set this up you need about 3GB per machine at least, which means having a wee RAC instance on your notebook is no longer viable.