OraOpenSource
Purge of the log table not releasing space
We have had an increase in the logged activity for the process we have written which has caused concerns with our DBA's concerning the amount of space being consumed in our production environment. I have made sure that we are only recording errors and warnings (this had been reset to defaults earlier in the week), We are also only retaining back one week, To clean up as much as possible I ran a purge of all records except warnings and errors. While it did remove a large number of records the space was not released. Is there a recommended process to free the space used by the logger table when a purge has been completed?
Below are the counts of records before and after the purge
Before
| TRUNC(TIME_STAMP) | COUNT(*) |
|---|---|
| 05/28/2020 00:00:00 | 1 |
| 08/23/2021 00:00:00 | 800,792 |
| 08/24/2021 00:00:00 | 880,383 |
| 08/25/2021 00:00:00 | 965,858 |
| 08/26/2021 00:00:00 | 3,446,552 |
| 08/27/2021 00:00:00 | 4,580,283 |
| 08/28/2021 00:00:00 | 4,742,126 |
| 08/29/2021 00:00:00 | 4,829,186 |
| 08/30/2021 00:00:00 | 1,390,725 |
After the purge
| TRUNC(TIME_STAMP) | COUNT(*) |
|---|---|
| 05/28/2020 00:00:00 | 1 |
| 08/24/2021 00:00:00 | 99,219 |
| 08/25/2021 00:00:00 | 111,753 |
| 08/26/2021 00:00:00 | 306,347 |
| 08/27/2021 00:00:00 | 701,502 |
| 08/28/2021 00:00:00 | 858,699 |
| 08/29/2021 00:00:00 | 897,930 |
| 08/30/2021 00:00:00 | 1,044,552 |
| 08/31/2021 00:00:00 | 416,667 |
I usually do an alter table shrink space: https://oracle-base.com/articles/misc/alter-table-shrink-space-online
