-
Dropping partitioned index
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
-
you cannot drop partitione index, you can rebuild local partitioned indexes though
-
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.
-
well it says it does not exists huh
select * from dba_ind_partitions where index_name='BM7'
-
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
-
Code:
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
-
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.
-
well then how come I can do it and you cannot...
-
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.
-
Your index name isn't mixed case or anything is it, or owned by a different user?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|