Printable View
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... :o
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?
No to both questions I'm afraid.
Quote:
Originally posted by 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.
Jus check this might be the case.Had u created a index or partioned index with the name in doube quote ("indx_name")...ther lies the probs.
Abhay.Code:
SQL> alter index emp_id rebuild;
alter index emp_id rebuild
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> alter index "emp_id" rebuild;
Index altered.
gues we posted almost same time.Quote:
Originally posted by slimdave
Your index name isn't mixed case or anything is it, or owned by a different user?
the thing is you first said you get
ORA-01418: specified index does not exist
then how come you can rebuild an index which does not exist?
then it seems that you can see the index in dba_ind_partitions so I am wondering, are you rebuilding the index with the proper user?
Hi, pando. When is your new book being released: "How to Lose Friends and Alienate People?"