Sorry I was on vacation last week and had no access to email (welcome first ;) ). I will check this out and report back. Thanks.
Brett -----Original Message----- From: Kristian Waagan [mailto:[email protected]] Sent: Monday, July 19, 2010 7:53 AM To: [email protected] Subject: Re: How to best constrain database space with many records being inserted and deleted On 06.07.10 20:09, Bergquist, Brett wrote: > > I have a situation where my system is adding about 600K records a day > to a table and also deleting about 600K records a day. Basically it > is keeping 30 days worth of history of some network management service > level agreement data. So each day about 600K new tests are performed > and recorded in the database and each day after the 30 day mark, about > 600K old records are purged out. On average there is about 18 million > records in the table. > Hi Brett, Are you able to determine if the growing conglomerate is a heap or a btree? Do to that, find the largest file(s) in the seg0 directory of your database. Convert the number from hex to dec, i.e. 'c300.dat' -> 768. Then do something like: select isindex, descriptor from sys.sysconglomerates where conglomeratenumber = ?; (? would be 768 for the example file) Regards, -- Kristian > I have little to no down time for database maintenance. Maybe 2 hours > per month maximum. What I am seeing is that the database is growing > and it does not seem to be reusing the deleted space. Should it be? > The records being inserted are exactly the size of the records being > deleted. > > I know that I could use the SYSCS_UTIL.SYSCS_COMPRESS_TABLE to reclaim > the space, but I am not so interested in giving back to the OS, but > rather ensuring the space available from the deleted records is > reused. I have attempted to reclaim the space with this command, > however, and about 5 hours of time is just too much. > > I also see there is a SYS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE but I am > not sure what the PURGE_ROW option is going to do. It sounds like it > is something that I want to look into, but the documentation is not > clear enough if that is what I need. > > Thanks in advance. > > Brett // >
