Re: Why do I still see index partitions when I dropped the

From: <Cherie_Machler_at_gelco.com>
Date: Fri, 11 May 2001 14:33:04 -0700
Message-ID: <F001.00301960.20010511143940@fatcity.com>

Ron,

Thanks for your kind reply.

I'll give it a try.

Cherie

                                                                                       
                        
                    "Ron Rogers"                                                       
                        
                    <RROGERS_at_galo        To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>   
                    ttery.org>           cc:                                           
                        
                    Sent by:             Subject:     Re: Why do I still see index 
partitions when I dropped   
                    root_at_fatcity.        the                                           
                        
                    com                                                                
                        
                                                                                       
                        
                                                                                       
                        
                    05/11/01                                                           
                        
                    02:21 PM                                                           
                        
                    Please                                                             
                        
                    respond to                                                         
                        
                    ORACLE-L

Cherie,
I take it that you built your indexes as create index xxx on table(col) local
(partition x1 on tablespace 95,
partition x2 on tablespace 96......)
then you truncated the table in the partition 95 and then dropped the partition.
If I remember correctly the index for the dropped partition is still there and
you can clean it up by using the (alter index xxx rebuild partition x1) if the
partition still exists but is empty. In your case I think that you will have to rebuild the complete index again because you have changed the make-up of the table layout.
Updates and corrections please.
ROR mª¿ªm

>>> Cherie_Machler_at_gelco.com 05/11/01 01:11PM >>>

This one's got me stumped. We are on 8.0.4 on Sun Solaris 2.6.

I have some locally indexed partitions on a table that's partitioned by time. I dropped the '96 and '95 partitions of the underlying table and I no longer see them. I used the following statements:

alter table xx truncate partition 95 drop storage; alter table xx drop partition 95;

According to the documentation, the corresponding '95 and '96 index partitions (for a locally indexed table which I confirmed that it is using the LOCALITY column of dba_ind_partitions) should be dropped automatically. However, when I query dba_ind_partitions view, I can still see them.

Documentation says I can not specifically drop index partitions of locally indexed tables (can only do this for globally indexed tables). I actually tried to drop these partitions and Oracle gave me an error message saying I couldn't do it because it was a local index.

I thought these index partition 95 and 96 would go away. Why do I still see them? We will rebuild the index tonight but I can't rebuild the whole index right now - too much performance degredation.

I'm just curious about what is going on. Do I need to refresh something? Is there anything low-impact I can do right now to make these index partitions go away? Do I even need to care?

Thanks for any kind soul with answers.

Cherie Machler
Gelco Information Network

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ron Rogers
INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message