still fresh memories: nls_length_semantics=CHAR again
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 22 Jan 2014 11:53:14 +0200
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
Other related posts:
- » still fresh memories: nls_length_semantics=CHAR again - Laimutis . Nedzinskas
