Dropping partitioned index

[Fiona]

Is it at all possible to drop partitioned index from just one of the partition?

I have recently did a partition swap - but I can't seem to rebuild the partitioned index onto the new tablespace. Rather than dropping the index across all partitions, I wanted to know if it can be done locally.

DB version is 8.1.7., OS is Windowns NT 4.0

Ta
Fiona

[pando]

you cannot drop partitione index, you can rebuild local partitioned indexes though

[Fiona]

I did try and rebuild it, only to get error message

ALTER INDEX BM7 REBUILD PARTITION P9 TABLESPACE P_3_NEW;
ERROR at line 1:
ORA-01418: specified index does not exist

DBA_SEGMENTS table clearly shows that old tablespace contains index BM7 and not in P_3_NEW where I want to rebuild it.

[pando]

well it says it does not exists huh

select * from dba_ind_partitions where index_name='BM7'

[Fiona]

I tried dba_ind_partitions for index BM7, and it says it is still in the old tablespace. Argh!

Looks like I have no choice but to drop the index all together and recreate it in correct tablespaces.

Thank you for the help anyway.

Fiona

[pando]

SQL-SSESMADT001-E81ADM0>select index_name, partition_name, tablespace_name from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PARTEMP_PK                     PARTEMP2                       USERS
PARTEMP_PK                     PARTEMP3                       USERS
PARTEMP_PK                     PARTEMP4                       USERS
PARTEMP_PK                     PARTEMP5                       USERS
PARTEMP_PK                     PARTEMP6                       USERS
PARTEMP_PK                     PARTEMP1                       USERS


SQL-SSESMADT001-E81ADM0>alter index partemp_pk rebuild partition partemp2 tablespace tools;

Indice modificado.

SQL-SSESMADT001-E81ADM0>select index_name, partition_name, tablespace_name from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PARTEMP_PK                     PARTEMP2                       TOOLS
PARTEMP_PK                     PARTEMP3                       USERS
PARTEMP_PK                     PARTEMP4                       USERS
PARTEMP_PK                     PARTEMP5                       USERS
PARTEMP_PK                     PARTEMP6                       USERS
PARTEMP_PK                     PARTEMP1                       USERS

6 filas seleccionadas.

I think you are joking heh

[Fiona]

Joking? Would you care to explain in what way I might be joking for me, as I have no recollection of telling any jokes to you.

I, for one, am certainly not laughing.

[pando]

well then how come I can do it and you cannot...

[Fiona]

That's what I'm trying to bloody find out. Well done to YOU for being able to do what I can't on YOUR database, but unfortunately, it doesn’t on mine and the reason behind that is what I'm trying to find out.

I know the syntax on how to rebuild partitioned index. I tried it. It didn't work. I want to find out why. I was under impression I could get some advice and help from those with more knowledge, but instead all I get is mocked.

You won’t mind if I don't thank you for making me feel patronised and ridiculed in public forum.

[slimdave]

Your index name isn't mixed case or anything is it, or owned by a different user?