RE: Index in SUMDELTA$
Date: Mon, 22 Jun 2009 17:09:19 +0100
Apologies. I don't think I made myself very clear originally.
The table was small (only about 136 rows) and was only 55Mb in size. It was the index that was taking up all the space. We rebuilt this index and it shrunk back to 1Mb from 344Mb.
There was a bug (a few I think) where the table wasn't clearing up its space but I have been told that they were fixed in 10.2.0.3 (which we are at).
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: 22 June 2009 17:06
To: Robertson Lee - lerobe; oracle-l_at_freelists.org Subject: Re: Index in SUMDELTA$
Possibly you've got a number of materialized views with materialized view logs (possibly on partitioned tables), and you've been doing a lot of 'insert /*+ append */ - which have been followed by delayed refreshes.
Each /*+ append */ results in a row inserted into sumdelta$, and the nature of the index on that table is allows it to become degenerate if you allow some of your refreshes to delay a long time.
It is a little odd that the table is that large - it suggests you may have managed to declare a couple of materialized views that you aren't refreshing any more (possibly snapshots in remote databases) - or that you've found a bug where Oracle doesn't clear sumdelta$ properly.
Author: Cost Based Oracle: Fundamentals
The Co-operative Oracle Users' FAQ
- Original Message ----- From: "Robertson Lee - lerobe" <Lee.Robertson_at_acxiom.com> To: <oracle-l_at_freelists.org> Sent: Monday, June 22, 2009 1:10 PM Subject: Index in SUMDELTA$
AIX 6 Got alerted that our SYSTEM tablespace was getting full and on checking the space taken up by the objects in there noticed that we have an index (IN_SUMDELTA$) on the table SUMDELTA$, taking up 344Mb of SYSTEM tablespace. The table is only 55Mb.
I don't know too much about this but a quick google showed me that it is tied to materialized views. Not much out there on Google other than that and I don't have access to Metalink at the moment as our SAC has changed and I am awaiting authorization.
I can see that there are a few bugs around about the table not clearing down on refreshes of the MV but nothing with regards to the index. Any advice or links to articles would be much appreciated. I am reluctant to rebuild the index on one of these "SYSTEM" tables.
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Received on Mon Jun 22 2009 - 11:09:19 CDT