create database might fail with 11.2.0.2

I hit a nice bug at a test migration from 10.2.0.5 to 11.2.0.2.
As it's easier to convince Oracle Support of a problem if the testcase is just simple, a colleague reduced the testcase to


cretate a small pfile:

db_name=test
db_block_size=2048
db_create_file_dest=/tmp

and run

create database character set al32utf8;

the result is:

create database character set al32utf8
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/doptim.bsq' near line 416
ORA-00604: error occurred at recursive SQL level 1
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Process ID: 16723
Session ID: 47 Serial number: 1

For Orale Support this really was enough to file Bug:10410249 towards development.

If you have a Database with blocksize of 2k and a multibyte characterset, beware!

So far enough for Oracle Support. Just to provide some more insights:
the upgrade failed at
@catupgrd.sql
with

SQL> begin
 2    dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
 3    dbms_stats.delete_table_stats('SYS', 'USER$MIG');
 4    dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG',  estimate_percent => 100,
 5                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 6    dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
 7                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 8  end;
 9  /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2

I did not know why dbms_stats seems to be broken. So I tried to run catupgrd.sql again - same problem.

Next step: recompile dbms_stats manually.
at running

@prvtstas.plb

it showed me there was an error at line 2175.
If you are curious what's going on there:

2175 CURSOR GET_COL_GROUP_USAGE(OWNER VARCHAR2, TABNAME VARCHAR2) IS
2176 SELECT CU.OBJ# OBJN, CU.COLS,
2177    (CASE WHEN BITAND(CU.FLAGS, 1) = 1 THEN 'FILTER ' ELSE '' END) ||
2178    (CASE WHEN BITAND(CU.FLAGS, 2) = 2 THEN 'JOIN ' ELSE '' END) ||
2179    (CASE WHEN BITAND(CU.FLAGS, 4) = 4 THEN 'GROUP_BY ' ELSE '' END) USAGE,
2180    CU.FLAGS USAGEFLG
2181 FROM SYS.COL_GROUP_USAGE$ CU
2182 WHERE CU.OBJ# = (SELECT O.OBJ# FROM SYS.OBJ$ O, SYS.USER$ U
2183                     WHERE O.OWNER# = U.USER#
2184                       AND U.NAME = OWNER
2185                       AND O.NAMESPACE = 1
2186                       AND O.REMOTEOWNER IS NULL
2187                       AND O.LINKNAME IS NULL
2188                       AND O.SUBNAME IS NULL
2189                       AND O.TYPE# = 2
2190                       AND O.NAME = TABNAME)
2191   ORDER BY ...

I checked COL_GROUP_USAGE$ - but it did not exist!

Some grep in $ORACLE_HOME/rdbms/admin brought me to c1102000.sql
There I can read:

-- #(9577300) Column group usage
create table col_group_usage$
(
 obj#              number,                                 /* object number */
 /*
  * We store intcol# separated by comma in the following column.
  * We allow upto 32 (CKYMAX) columns in the group. intcol# can be
  * upto 1000 (or can be 64K in future or with some xml virtual columns?).
  * Assume 5 digits for intcol# and one byte for comma.
  * So max length would be 32 * (5+1) = 192
  */
 cols              varchar2(192 char),              /* columns in the group */
 timestamp         date,     /* timestamp of last time this row was changed */
 flags             number,                                 /* various flags */
 constraint        pk_col_group_usage$
 primary key       (obj#, cols))
 organization index
 storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/

As I tried to create that table manually, I recieved my ORA-01429.

We discussed some workarounds internally.
I created the IOT (without approval from Oracle Support) with cols varchar2(192 byte). Guess what, it worked! Also @catupgrd.sql was fine afterwards.
As I'm sure in this particular DB there are only column names with plain ASCII, this will not be an issue in this particular DB. But some other suggestions like creating a heap table or IOT without the cols as part of the index definition. This might result in performance problems of dbms_stats, but does not affect any logic (as my WA does).

UPDATE:
Patch:10410249 is provided for this issue now. It's labeled generic, as it contains only some sql files for $ORACLE_HOME/rdbms/admin and the proper patch (and rollback) scripts.