1 Reply Latest reply: Dec 13, 2012 2:19 PM by JustinCave RSS

    Can I use shrink to a active (very insert-delete operations perform) table

    973995
      Hello Guys,

      I have got very simple question. According to an article (link: http://www.dba-oracle.com/t_automatic_segment_space_management.htm) the following quote is written
      Beware:  Using ASSM can hinder database DML performance, and most Oracle experts will use manual freelists and freelist groups.  Click here for details on automatic segment space management internals.
      
      Using automatic segment space management is great for almost all Oracle applications, with the exception being super-high update tablespaces, which would use traditional freelists with multiple freelist groups.
      I just want to use shrink my table in order to reclaim unused space. However, I am performing many insert and delete operations on that table (Not update, only insert and delete). So, I am just wondering, if I use shrink, will my table be affected badly? Because I insert and delete thousands of rows from that table everyday.


      And my second question is how can I learn that my table is in ASSM (Automatic segment space management) to use shrink? Because it has been said that my table shoul be in ASSM in order to use shrink?
      select * from v$version;
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      "CORE     11.2.0.1.0     Production"
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      Regards
      Charlie
        • 1. Re: Can I use shrink to a active (very insert-delete operations perform) table
          JustinCave
          970992 wrote:
          I just want to use shrink my table in order to reclaim unused space.
          OK. You are sure that there is unused space and that Oracle isn't going to reuse that space automatically? If you are constantly inserting and deleting from a table, unless you are constantly deleting more data than you are inserting or you are using direct-path inserts to prevent Oracle from reusing the space freed up by the deletes, there is no reason to expect there to be unused space in the table.
          However, I am performing many insert and delete operations on that table (Not update, only insert and delete). So, I am just wondering, if I use shrink, will my table be affected badly? Because I insert and delete thousands of rows from that table everyday.
          Thousands of rows is a pretty minimal amount of activity.

          If you are going to shrink the table, you would generally want to do so while there is little to no activity on the table since you will need to at least briefly acquire a lock on the table. If you're only doing a few thousand DML operations of the course of a day, it shouldn't be hard to find a window where the shrink won't significantly impact DML.

          But, going back to my first point, I would tend to expect that you don't need to shrink the table in the first place.
          And my second question is how can I learn that my table is in ASSM (Automatic segment space management) to use shrink? Because it has been said that my table shoul be in ASSM in order to use shrink?
          Assuming you know what tablespace the table is in
          SELECT segment_space_management
            FROM dba_tablespaces
           WHERE tablespace_name = <<name of tablespace>>
          Justin