This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Dec 16, 2012 12:28 PM by 973995 Go to original post RSS
  • 15. Re: Can I use truncate and where clause in the same statement?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Visa versa, I am inserting more data than I am deleting. Therefore, the tables are getting bigger.
    If the tables are growing, you would expect that the tablespace will need to grow periodically as well. If you don't have it set to autoextend, you should be monitoring how full it is and proactively adding data files/ increasing the size of data files to accomodate the growth.

    Justin
  • 16. Re: Can I use truncate and where clause in the same statement?
    973995 Newbie
    Currently Being Moderated
    >
    If the tables are growing, you would expect that the tablespace will need to grow periodically as well. If you don't have it set to autoextend, you should be monitoring how full it is and proactively adding data files/ increasing the size of data files to accomodate the growth.
    >

    So, you say I don't have to use shrink for this problem, right?

    Charlie
  • 17. Re: Can I use truncate and where clause in the same statement?
    EdStevens Guru
    Currently Being Moderated
    970992 wrote:
    >
    If the tables are growing, you would expect that the tablespace will need to grow periodically as well. If you don't have it set to autoextend, you should be monitoring how full it is and proactively adding data files/ increasing the size of data files to accomodate the growth.
    >

    So, you say I don't have to use shrink for this problem, right?

    Charlie
    "Visa versa, I am inserting more data than I am deleting. Therefore, the tables are getting bigger."

    You have a bucket.
    Every day you pour two gallons of water into the bucket.
    Every day you draw 1 gallon of water out of the bucket.

    And you want to figure out a way to make the bucket smaller - to shrink it?

    What problem are you trying to solve?
  • 18. Re: Can I use truncate and where clause in the same statement?
    rp0428 Guru
    Currently Being Moderated
    >
    So, you say I don't have to use shrink for this problem, right?
    >
    That's correct - your problem is that you need a growing amount of space because you are inserting more data than is being deleted.

    So - you need a BIGGER bucket, not a smaller one.

    Your original question was this
    >
    If not, what you recommend me in order to decrase the High Water Mark level?
    >
    That assumes that you have already determined that you actually NEED to do that to solve your problem. Decreasing the HWM would be appropriate when the table will NOT be growing and you want to recover the unused space.

    Now you have finally told us the problem you are actually trying to solve
    >
    The reason that I want to shrink the tables is I get the following errors nowadays.

    ORA-01654 unable to extend index

    ORA-01653 unable to extend table

    *Cause: Failed to allocate an extent of the required number of blocks for
    an index segment in the tablespace indicated.
    *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
    files to the tablespace indicated.

    So, doesn't it mean that I got problems with high water mark?
    >
    Yes - you DEFINITELY have a problem with the HWM. But the problem is that Oracle can't move it HIGHER to make room for more records.

    Oracle will reuse space below the HWM for inserts so that space will not be 'wasted'.

    Your problem is that all of that space below the HWM is already being used and you don't have any more space to allocate to the table.

    THAT is the problem you need to address.
  • 19. Re: Can I use truncate and where clause in the same statement?
    973995 Newbie
    Currently Being Moderated
    >
    Oracle will reuse space below the HWM for inserts so that space will not be 'wasted'.

    >

    I am seriously wondering, is that documented? Because I though oracle cannot use space below HWM.

    >
    That's correct - your problem is that you need a growing amount of space because you are inserting more data than is being deleted.
    >

    Ok I need a growing amount of space. However, at the same time I want to reclaim emtpy spaces below HWM. So, I need use fewer amount of space than I am inserting. As a result, shouldn't I use shrink for this?
  • 20. Re: Can I use truncate and where clause in the same statement?
    973995 Newbie
    Currently Being Moderated
    >
    What problem are you trying to solve?
    >

    Ok the bucket is going to grow this is inevitable, But my problem is not this. I am just trying to reclaim unused space below HWM, so it is growing slowly.
  • 21. Re: Can I use truncate and where clause in the same statement?
    rp0428 Guru
    Currently Being Moderated
    >
    Ok I need a growing amount of space. However, at the same time I want to reclaim emtpy spaces below HWM. So, I need use fewer amount of space than I am inserting. As a result, shouldn't I use shrink for this?
    >
    Why waste the effort. Oracle will reuse the space below the HWM when you do inserts. You already said you insert more rows than you delete so all of these will get used up.

    The only time Oracle cannot use the space is when the space is too small to be useful. This is documented in the doc I pointed you to
    >
    Understanding Reclaimable Unused Space
    Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data.
    >
    Most uses of SHRINK that I have seen are totally unnecessary. If you follow the doc and run the segment advisor it will tell you if there is space that can be reclaimed and how much. But, again, for your use case even if you reclaim it Oracle will then have to reallocate new extents and then the HWM is just going to move higher again.

    So you pay a heavy price in Oracle doing a full table scan, updating many table blocks, updating ALL indexes, performance impact for other users accessing the table during the SHRINK and reallocating extents (maybe even some of the same extents) when you insert more data.

    Don't SHRINK unless you know it is necessary.
  • 22. Re: Can I use truncate and where clause in the same statement?
    EdStevens Guru
    Currently Being Moderated
    970992 wrote:
    >
    Oracle will reuse space below the HWM for inserts so that space will not be 'wasted'.

    >

    I am seriously wondering, is that documented? Because I though oracle cannot use space below HWM.
    Yes, it is documented. In the Concepts Manual. At http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#CIHFIIFB.

    And to quote from there:

    Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:*

    If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available.*

    If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.*

    >
    >
    That's correct - your problem is that you need a growing amount of space because you are inserting more data than is being deleted.
    >

    Ok I need a growing amount of space. However, at the same time I want to reclaim emtpy spaces below HWM. So, I need use fewer amount of space than I am inserting. As a result, shouldn't I use shrink for this?
    No.
    You seem to be under the mistaken impression that Oracle is incapable of managing its own space.

    Edited by: EdStevens on Dec 16, 2012 12:14 PM
  • 23. Re: Can I use truncate and where clause in the same statement?
    973995 Newbie
    Currently Being Moderated
    Thanks to everybody who help me in this thread. Thanks a lot guys.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points