RE: Index in SUMDELTA$
Date: Mon, 22 Jun 2009 17:09:19 +0100
Message-ID: <F1A191B056E5E04EA2134D842F9396F819B3233E_at_sunmsx01.Corp.Acxiom.net>
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).
Regards
Lee
-----Original Message-----
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.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- 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$
Guys,
Oracle 10gR2
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.
Cheers
Lee
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.
Thank You.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 22 2009 - 11:09:19 CDT