Re: weekend-fun with 11.2.0.2
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: Jeremiah Wilton <jwilton@xxxxxxxxxxxxx>
- Date: Fri, 17 Dec 2010 20:20:15 +0100
I agree, that's broken.
To share a wider picture here my path how I discovered it (slightly striped):
I tried to migrate my 2k blocksize al32utf8 DB from 10.2.0.5 to 11.2.0.2.
the
@catupgrd.sql
failed 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
ok. dbms_stats seems to be broken. so let's try to run catupgrd.sql again -
same problem.
so I tried to 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.
My own workaround (without approval of ORacle Support!) was to create the IOT
with cols varchar2(192 byte) - as I'm sure all column names in this DB will
only be plain ASCII, this should be fine just to make the db available again.
Guess what? it worked ;-)
This issue is filed at MOS as BUG:10410249 - but development has not commented
it, yet.
I hope that answered some questions.
Martin
Am 17.12.2010 um 18:57 schrieb Jeremiah Wilton:
> On Dec 17, 2010, at 9:29 AM, Martin Berger wrote:
>
>> If anyone want to have fun, just run this simple testcase:
>> use your 11.2.0.2 rdbms binaries (linux and hp-ux tested, but others will
>> also work).
>>
>> create a small pfile:
>>
>> db_name=test
>> db_block_size=2048
>> db_create_file_dest=/tmp
>>
>> set any ORACLE SID and run
>>
>> create database character set al32utf8;
>>
>> I'd like to see your results ;-)
>> (I can give some more infos, do not waste time for investigations)
>
> Wow that's broken. 2K block size doesn't really seem like much of an edge
> case. 8k block size works fine, but you already know that.
>
> SQL> create database character set al32utf8;
> 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: 2246
> Session ID: 87 Serial number: 3
>
> Removed db_block_size from the pfile, and"
>
> SQL> create database character set al32utf8;
>
> Database created.
>
> --
> Jeremiah Wilton
> http://www.bluegecko.net
>
>
>
Other related posts:
