DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Pissed off at 9iR2

  1. #1
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Pissed off at 9iR2

    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.

    * sigh *
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    200hrs@$125/hr...I can live with that...
    Jeff Hunter

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    200hrs@$125/hr...I can live with that...
    So could i, if that's what it was.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587

    Re: Pissed off at 9iR2

    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)

    although as an hourly-rate consultant it has paid for my landscaping.
    No complaining there though...
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: Pissed off at 9iR2

    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Anyone worked with Teradata? I have always been told Teradata owns Oracle in DWH.

    It's really a shame you cannot add or drop column with compressed tables

    What mod do you run against obj$ to prevent the error ypou mentioned :-?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...

    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Pissed off at 9iR2

    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"

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: Pissed off at 9iR2

    Originally posted by abhaysk
    ?????????
    Indeed.

    Bug No. 2093670
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Re: Pissed off at 9iR2

    Originally posted by slimdave
    Indeed.

    Bug No. 2093670
    Dave :

    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"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width