This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Oct 26, 2012 3:52 AM by viconstg RSS

How to limit the table size?

viconstg Journeyer
Currently Being Moderated
Hello,

I have a problem with one table.. First of all:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

The problem table resides in a locally managed tablespace. About 10 millions records is added in this table every day. After 36 hours all these records moved to another (partitioned) table, so the size of data in the problem table always about 75 Gb. But the size of table is reached 157 Gb today, and it still growing. The results of dbms_space.space_usage are showed below:
Size of blocks with:
0-25% free space: 4726784
25-50% free space: 17301504
50-75% free space: 24920064
75-100% free space: 102418669568
full blocks: 54761594880
Thus, a lot of blocks have 75-100% free space but the table constantly growing: during last 9 days the size increased from 123 to 157 Gb.

Please advise how to stop the table growing? It there any way to limit the table size in locally managed tablespace?

Thanks in advance

Edited by: viconstg on 09.10.2012 16:19
  • 1. Re: How to limit the table size?
    Girish Sharma Guru
    Currently Being Moderated
    Please advise how to stop the table growing? It there any way to limit the table size in locally managed tablespace?
    1.Fix user quota for the tablespace
    2.Use before insert trigger

    The only thing I can think of is, a before insert trigger on this table. The trigger runs off to dba_extents (or dba_segments), works out how big the table is, and if the insert will cause the table to extend, don't allow the insert to happen.

    I can't see what query you'd write to predict if an insert would take it over 200MB off the top of my head, so you might have to allow the table to extend past that that by (say) 1MB, and then ban further inserts after that point. But it would probably be close enough...

    HJR @ LMT - Limit size of table

    3.A check constraint solution by MichaelS
    How to limit rows for a table?

    Regards
    Girish Sharma
  • 2. Re: How to limit the table size?
    Iordan Iotzov Expert
    Currently Being Moderated
    Is the table IOT? How is the data moved to the other (partitioned) tables?? How is the data loaded into the problem table (direct load, parallel,…)?

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 3. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    No, it's not IOT table, it's usual relational table.

    Data is inserted by user transactions (it's high-loaded OLTP system), then copied to another table, and after 36 hours deleted from the source table.
  • 4. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    Dear Girish Sharma, thank you very much for your reply and references provided. But it's not my case: I cannot disallow the insertion, and I cannot limit the user -- he owns all objects within this tablespace. I just would like to use the free space within the occupied extents instead of creating the new one. Is it possible?
  • 5. Re: How to limit the table size?
    Iordan Iotzov Expert
    Currently Being Moderated
    I assume you use ASSM…      
    Is the problem table partitioned? Some types of partitioning can restrict where the new records could reside.
    What are the settings for PCTFREE and PCTUSED?

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 6. Re: How to limit the table size?
    Girish Sharma Guru
    Currently Being Moderated
    I just would like to use the free space within the occupied extents
    Extents in a segment are "used" -- i.e. there are never "free" extents in a segment.

    Free Extents may exist in a Tablespace. A Free Extent is allocated to a segment that needs a new extent. Once an extent is allocated to a segment, it is "used" and belongs to the segment. The extent must be explicitly deallocated (i.e. "freed") be either of
    a. ALTER TABLE tablename DEALLOCATE UNUSED
    b. ALTER TABLE tablename SHRINK
    c. ALTER TABLE tablename MOVE


    Hemant K Chitale @ No.of free extents in a given segment in a particular tablepsace

    Regards
    Girish Sharma
  • 7. Re: How to limit the table size?
    jgarry Guru
    Currently Being Moderated
    It "sounds like" you are having an issue with adding rows while an uncommitted huge delete is happening, temporarily confusing the decision about which blocks are free. See here for an example: http://jonathanlewis.wordpress.com/2011/01/19/assm-again/

    Normally you don't want to commit until a unit of work is done, but if you research and find the above is what is happening, you might want to break the delete into more transactions (for a temporary fix). Your situation might be worsening simply because you have more bitmap size, if that is the case you'd want to shrink, too.
  • 8. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    Thank you very much for your reply, Iordan.
    Yes, I use ASSM.
    No, the problem table is not partitioned. For this table PCTFREE = 10, PCTUSED is not set (because it's not useful for ASSM).
  • 9. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    jgarry wrote:
    It "sounds like" you are having an issue with adding rows while an uncommitted huge delete is happening, temporarily confusing the decision about which blocks are free. See here for an example: http://jonathanlewis.wordpress.com/2011/01/19/assm-again/
    Thank you very much, Joel. I think you're right. The regular (every 10 minutes) deletion from this table is performed by 100K rows portions. Insertions is made continuously, i.e. the deletions and the insertions are simultaneous.
    Normally you don't want to commit until a unit of work is done, but if you research and find the above is what is happening, you might want to break the delete into more transactions (for a temporary fix). Your situation might be worsening simply because you have more bitmap size, if that is the case you'd want to shrink, too.
    Thank you, I'll try to split the deletions into smaller portions. I've used the shrink space for this table a month ago, but I've faced with the complete lock of table during 53 minutes (!) what is unacceptable, so I wouldn't use the same procedure again.
  • 10. Re: How to limit the table size?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Is it the same session that issues INSERTs and DELETEs ? (there may be multiple session but my question is whether one session does both operations)
    Interesting anomaly if it is still present in 11.2.0.2

    Hemant K Chitale
  • 11. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    Girish Sharma wrote:
    I just would like to use the free space within the occupied extents
    Extents in a segment are "used" -- i.e. there are never "free" extents in a segment.
    I spoke about free space within occupied extents, not about "free extents in a segment" :)
  • 12. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    No, INSERTs and DELETEs is made by different sessions (the cleanup is performed by separate process).
  • 13. Re: How to limit the table size?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    So it is possible that the INSERT is issued even before the DELETE is committed.


    Hemant K Chitale
  • 14. Re: How to limit the table size?
    viconstg Journeyer
    Currently Being Moderated
    Hemant K Chitale wrote:
    So it is possible that the INSERT is issued even before the DELETE is committed.
    Yes, for certain many INSERTs issued before the DELETE is committed.
1 2 Previous Next

Legend

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