Dropping partitioned index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Dropping partitioned index

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you cannot drop partitione index, you can rebuild local partitioned indexes though

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    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.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well it says it does not exists huh

    select * from dba_ind_partitions where index_name='BM7'

  5. #5
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  7. #7
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    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.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well then how come I can do it and you cannot...

  9. #9
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    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.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Your index name isn't mixed case or anything is it, or owned by a different user?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width