Today has triggered an alarm about lack of space in a tablespace, examining what there are into the tablespace, a table appears it’s a historical data, occasionally is a process deleting from this table, but to grow ahead this is not reusing space. After reviewing the ASH I see basically two types of DML statements are made against this table, a delete that responsible for maintaining only the last year and a insert keeping the most recent information. Seeing the same judgment was confirmed my suspicions that uses an append, the statement is as follows;
INSERT /*+ APPEND */ INTO …
This always inserting HWM above. All the space behind the High Water Mark is being lost. Of course, this is the worst case.
- To recover the space would have to rebuild the table.
- You can reclaim space by removing the append hint, but it perform the insert more slower, especially if it is massive like this case.
HTH – Antonio NAVARRO