One of our clients had some problems with the Backup Service in their GIS system. They turned it off for about 2 months and at the end of this period there were about 4M records with ARCHIVE_FLAG = 0 in the ARCHIVE_INFO table. The DB size had increased to hundreds of GBs. I decided to change all of the records with ARCHIVE_FLAG = 0 to ARCHIVE_FLAG = 2 and ARCHIVE_DATE = “now() + 1 year” to make them “invisible” for both the Backup and Purge Services. Then I turned the Backup Service back (thus I stopped the DB growing) and started “returning” those changed records back day by day. So, the index/backup/purge services got back to the rut.
Important: it’s just an idea, not the direct instructions. You should understand how it works to make such changes… If you don’t understand the SI/GIS DB you mustn’t do anything with it except maybe select queries.
Here is the SQL query I used (I ran it several times, day by day to avoid excessive DB loading):
update ARCHIVE_INFO set ARCHIVE_FLAG = 2, ARCHIVE_DATE = ARCHIVE_DATE + 365 where TO_CHAR(ARCHIVE_DATE, 'yyyy-mm-dd') = '2014-01-01' and ARCHIVE_FLAG = 0; commit;