still fresh memories: nls_length_semantics=CHAR again

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Wed, 22 Jan 2014 11:53:14 +0200
Message-ID: <OFE909A1F7.C55FC608-ONC2257C68.00361439-C2257C68.0036516F_at_seb.lt>

That might help someone. Posting till my memories (and a degree of anger) are still fresh:

Check nls_length_semantics=BYTE again. And again. And again.

When collecting statistics got  ORA-20002: Version of statistics table … is too old.
Please try upgrading it with dbms_stats.upgrade_stat_table

Know why ? Because the statistics table was created with nls_length_semantics=CHAR.

Oracle compares etalon table SYS.IMPDP_STATS with your stats table and yes, column sizes differ because SYS oh, yes has column size 30 but your table has (oh, yesss) column size 120:

SELECT COUNT(*) FROM ( SELECT C.NAME, C.TYPE#, C.LENGTH, COUNT(*) OVER () COLCNT FROM USER$ U, OBJ$ O, COL$ C WHERE U.NAME = :B2 AND U.USER# = O.OWNE R# AND O.TYPE# = 2 AND O.NAME = :B1 AND O.OBJ# = C.OBJ# MINUS SELECT C.NAME, C.TYPE#, C.LENGTH, COUNT(*) OVER () COLCNT FROM USER$ U, OBJ$ O, COL$ C
WHERE U.NAME = :B4 AND U.USER# = O.OWNER# AND O.TYPE# = 2 AND O.NAME = :B3 AND O.OBJ# = C.OBJ#) WHERE ROWNUM < 2
END OF STMT
BINDS #18446744071462517720:
 Bind#0
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1206001 frm=01 csi=873 siz=128 off=0   kxsbbbfp=ffffffff7a1d2050 bln=32 avl=09 flg=05   value="USER123"
 Bind#1

  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=873 siz=0 off=32
  kxsbbbfp=ffffffff7a1d2070  bln=32  avl=07  flg=01

  value="STATTAB"
 Bind#2

  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=873 siz=0 off=64
  kxsbbbfp=ffffffff7a1d2090  bln=32  avl=03  flg=01

  value="SYS"
 Bind#3

  oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=01 csi=873 siz=0 off=96
  kxsbbbfp=ffffffff7a1d20b0  bln=32  avl=11  flg=01

  value="IMPDP_STATS"

( http://laimisnd.wordpress.com/2014/01/22/nls_length_semanticschar-again/ )

---

Please consider the environment before printing this e-mail

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 22 2014 - 10:53:14 CET