3 Replies Latest reply: Jul 12, 2013 1:59 AM by Dileepakunuri RSS

    10.2 to 11.2 upgrade ORA-20011/ORA-12702

    user13501755

      ORA-20011/ORA-12702 error received post upgrade (in 11g) when running full database stats,

         stats worked in 10g. upgrade appeared successful, no errors.

       

      database:

      freshly upgraded from 10.2.0.3.0 => 

      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

      PL/SQL Release 11.2.0.1.0 - Production

      CORE    11.2.0.1.0      Production

      TNS for Solaris: Version 11.2.0.1.0 - Production

      NLSRTL Version 11.2.0.1.0 - Production

       

      server:

      SunOS xxxxxx 5.10 Generic_Virtual sun4v sparc sun4v

      64-bit sparcv9 kernel modules

       

      SQL> exec dbms_stats.gather_database_stats;

      BEGIN dbms_stats.gather_database_stats; END;

      *

      ERROR at line 1:

       

      ORA-20011: Approximate NDV failed: ORA-12702: invalid NLS parameter string used in SQL function

       

      ORA-06512: at "SYS.DBMS_STATS", line 20508

      ORA-06512: at "SYS.DBMS_STATS", line 20993

      ORA-06512: at "SYS.DBMS_STATS", line 21150

      ORA-06512: at "SYS.DBMS_STATS", line 21109

      ORA-06512: at line 1

       

      SQL> exec dbms_stats.gather_database_stats_job_proc;

       

      PL/SQL procedure successfully completed.

       

      SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent =>100, block_sample=>FALSE, method_opt=>'for all columns size auto', degree=>null, cascade=>true, no_invalidate=>false, options=>'GATHER STALE', gather_sys=>FALSE);

       

      PL/SQL procedure successfully completed.

       

      SQL> !env | grep NLS

      NLS_LANG=american

      NLS_DATE_FORMAT=YYYY/MM/DD HH24:MI:SS

       

      SQL> select userenv('LANGUAGE') from dual;

      USERENV('LANGUAGE')

      ----------------------------------------------------

      AMERICAN_AMERICA.AL32UTF8

       

      SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

      PARAMETER

      ----------------------------------------------------------------

      VALUE

      ----------------------------------------------------------------

      NLS_CHARACTERSET

      AL32UTF8

      NLS_NCHAR_CHARACTERSET

      AL16UTF16

       

      any ideas, please? I haven't seen this elsewhere..