To register for an Internet.com membership to receive newsletters and white papers, use the Register button ABOVE.
To participate in the message forums BELOW, click here
Well, we "upgraded" to 9iR2 a few months ago, and we're all fckd up.
Firstly, we lost the ability to do CTAS on a 32K tablespace. Rather inconvenient.
Secondly, we implemented data segment compression, and now we have to keep running mods against the sys.obj$ table to prevent "block too fragmented to build bitmap index" errors.
Thirdly, after implementing data segment compression we found that you can neither ADD nor DROP columns from the table -- you have to uncompress the whole damn thing. Needless to say, this was an unpleasant surprise.
Fourthly, on our major fact tables we can NO LONGER USE BITMAP INDEXES due to a bug that Oracle say may not be fixed for our platform until 10i.
On a personal note, these problems have cost me about 200 hours with my family over the past few months, although as an hourly-rate consultant it has paid for my landscaping.
Anyhoo, at this rate our upgrade path will skip 10i and go directly to Teradata.
Originally posted by slimdave Well, we "upgraded" to 9iR2 a few months ago, and we're all fckd up.
Firstly, we lost the ability to do CTAS on a 32K tablespace. Rather inconvenient.
Secondly, we implemented data segment compression, and now we have to keep running mods against the sys.obj$ table to prevent "block too fragmented to build bitmap index" errors.
Thirdly, after implementing data segment compression we found that you can neither ADD nor DROP columns from the table -- you have to uncompress the whole damn thing. Needless to say, this was an unpleasant surprise.
Fourthly, on our major fact tables we can NO LONGER USE BITMAP INDEXES due to a bug that Oracle say may not be fixed for our platform until 10i.
at this rate our upgrade path will skip 10i and go directly to Teradata.
* sigh *
Dave don't get me wrong here, but didn't you try all this out in your dev/test environemnt before upgrading your production databases. Either way you've learnt something...(I guess)
Quote:
although as an hourly-rate consultant it has paid for my landscaping.
No complaining there though...
__________________ Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
Originally posted by Tarry Dave don't get me wrong here, but didn't you try all this out in your dev/test environemnt before upgrading your production databases. Either way you've learnt something...(I guess)
We tried a lot of things, but evidently not enough, and our test box is close-but-not-the-same as prod.
For example, it's only certain of our fact tables that we can't bitmap index-- no-one seems to know why, and it worked fine under test.
We didn't try adding/dropping columns from a compressed table, 'cos it's not part of our normal operations to do so. It's was a new development that raised the issue.
Well, you know how it is, you have to draw the line somewhere. Whatever you test, you'll have missed something like a bug in applying a bitmap index to a non-foreign keyed nullable date type column having more than 10,00 rows and more than 4 distinct values, or some crap like that.
My real problem is with lack of responsiveness from Oracle on some of these issues -- having been outstanding for more than a month, with fixes backported to other platforms, you'd think it fairly straightforward to backport to a fairly common O/S like HPUX 64bit. Bugs i can handle, but the wait for fixes is driving me crazy.
I think that theoretically (meaning "if the feature sets work") then the gap is much closer in 9iR2 than in prior releases.
The obj$ update sets the expected maximum number of rows to a higher number, detailed in metalink note 194734.1 ...
Quote:
Error: ORA-28606 (ORA-28606)
Text: block too fragmented to build bitmap index (%s,%s)
---------------------------------------------------------------------------
Cause: The block(s) exceed the maximum number of rows expected when
creating a bitmap index. This is probably due to maximum slot
allowed set too low. The values in the message are: (slot number
found, maximum slot allowed)
Action: alter system flush shared_pool; update tab$ set spare1 = 8192
where obj# = (select obj# from obj$ where NAME= AND
owner# = ; commit;
.
I believe there is a similar workaround for a problem with "minimize rows per block" and partition exchanges, where spare1 is higher for the non-partitioned table than for the partitioned table.
I forgot to mention another problem. DBMS_STATS.GATHER_TABLE_STATS() with 'cascade=>true' won't work for function-based indexes. Or was that 8i? I forget now.
Originally posted by slimdave Fourthly, on our major fact tables we can NO LONGER USE BITMAP INDEXES due to a bug that Oracle say may not be fixed for our platform until 10i.
* sigh *
?????????
__________________
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
In our DW type DB (9.2.0.3 now), we have many BIT maps, but never encountered any problems, so just amused when u said "NO LONGER USE BITMAP INDEXES"...
Indeed I need to check once more now.
Abhay.
__________________
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Linear Mode
