This content has been marked as final. Show 1 reply
970992 wrote: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.
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.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>>