Weird behavoir in Oracle 9i
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Weird behavoir in Oracle 9i

  1. #1
    Join Date
    Mar 2001
    Posts
    149

    Weird behavoir in Oracle 9i

    Hi everyone,

    I have kinda strange situation that I need an explaination, I hope some of you can me a good hint. We have a large table that has alot of inserts and the table is growing at a fast pace. To minimize this growth, we implement a purge job that essentially delete a bunch of old records from this table. The strangest thing is that even after the purge job ran, the table keep growing, even though the number of records within that tables has been reduced significantly. From what I can see, the table is grabbing free blocks beyond the HWM. I thought Oracle would recycle the used blocks before attempting to use the free blocks above the HWM. Is there something I can do to rectify this issue. We're using 9iR2 with LMT and auto segment space mgmt. Thanks in advance!!

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    How you are inserting the data?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Jan 2007
    Posts
    14
    Consider table reorganization. After you have deleted the rows, export the table, then drop it and import the table again. That should solve your problem I suppose.

  4. #4
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Cool

    Hi,
    Could you let us know the way you measure the size of the table/segment?

    Thanks

  5. #5
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Wink

    Hi,
    You can also use alter table sch.tab_name move;
    But remember that u need to rebuild all the indexes involved with this table. If that is feasible for u then u can proceed and measure the size of the segment.

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    apart from what Thomasps said, what is your PCT_FREE settings? (if your not using segment space management AUTO)
    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"

  7. #7
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Do you use /*+ append */ hint for your table inserts by any chance? This is exactly what this hint was designed for.

  8. #8
    Join Date
    Mar 2006
    Posts
    11

    Cool

    Quote Originally Posted by newbie
    Hi everyone,

    I have kinda strange situation that I need an explaination, I hope some of you can me a good hint. We have a large table that has alot of inserts and the table is growing at a fast pace. To minimize this growth, we implement a purge job that essentially delete a bunch of old records from this table. The strangest thing is that even after the purge job ran, the table keep growing, even though the number of records within that tables has been reduced significantly. From what I can see, the table is grabbing free blocks beyond the HWM. I thought Oracle would recycle the used blocks before attempting to use the free blocks above the HWM. Is there something I can do to rectify this issue. We're using 9iR2 with LMT and auto segment space mgmt. Thanks in advance!!

    Hi,

    can you provide us with some additional answers?

    How are you loading the data? Are you using SQL*Loader? This tool can load above the HWM to gain speed.
    Why is this behaviour strange, did you not have this with Oracle8?
    I don't think rebuilding the table every each delete is a good idea. I myself restrain from rebuilding indexes every evening.
    There is an interesting Oracle doc:
    "How to stop defragmenting and start living : the definitive word on fragmentation" - doc in PDF *239049.pdf ( I don't remember the original ID)

    Regards,
    Richard.
    "Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by nuushona
    Consider table reorganization. After you have deleted the rows, export the table, then drop it and import the table again. That should solve your problem I suppose.
    If you're going to reorganise a table then export/import is about the worst way of doing it. ALTER TABLE ... MOVE is a much better method.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by abhaysk
    apart from what Thomasps said, what is your PCT_FREE settings? (if your not using segment space management AUTO)
    He is.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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