Issue while moving INDEX from one tablespace to other tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Issue while moving INDEX from one tablespace to other tablespace

  1. #1
    Join Date
    May 2008
    Posts
    2

    Issue while moving INDEX from one tablespace to other tablespace

    Actually, our aim is to move the objects of dictionary managed tablespace to the locally managed tablespace with out using the procedure

    dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

    The ‘INDX’ tablespace consists of 238 indexes. All the indexes are moved successfully from ‘INDX’ tablespace to ‘INDX_NEW’ tablespace except one.

    Below is the procedure we followed……



    1. We have an object of type ‘INDEX’ in the tablespace ‘INDX’ .

    select owner,segment_name,segment_type from dba_segments where tablespace_name='INDX'


    OWNER SEGMENT_NAME SEGMENT_TYPE
    DATA PK_ACK_ID INDEX


    2. Retrieving the object information

    select owner,object_name,object_type,object_id from dba_objects where object_name='PK_ACK_ID'

    OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
    DATA PK_ACK_ID INDEX 904327

    3. Retrieving the index information

    select owner,index_name,table_owner,table_name,status from dba_indexes where index_name='PK_ACK_ID'

    OWNER INDEX_NAME TABLE_OWNER TABLE_NAME STATUS

    DATA PK_ACK_ID DATA ACKNOWLEDGEMENT_TAB VALID

    4. Checking the table on which the index exists

    select owner,table_name,status from dba_tables where table_name='ACKNOWLEDGEMENT_TAB'

    OWNER TABLE_NAME STATUS
    DATA ACKNOWLEDGEMENT_TAB VALID

    5. Moving the index from tablespace ‘INDX’ to the new tablespace ‘INDX_NEW’

    6. alter index PK_ACK_ID rebuild tablespace INDX_NEW

    ORA-01418: specified index does not exist

    But ,it is saying that the object does not exists


    7. Even,When we describe the index ‘DATA.PK_ACK_ID’,we are getting the error

    Object DATA.PK_ACK_ID not found



    Please give us the solution as soon as possible for moving this index to new tablespace.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please run query below and post results...
    Code:
    select  owner,
            index_name,
            index_type,
            tablespace_name
    from    dba_indexes
    where   index_name = 'PK_ACK_ID' 
    ;
    Please post your queries and result sets properly, use CODE tags (#)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Talking

    Just a thought, did you try:

    Alter Index DATA.PK_ACK_ID Rebuild Tablespace INDX_NEW?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    May 2008
    Posts
    2
    The output of the following query is as follows:

    select owner,
    index_name,
    index_type,
    tablespace_name
    from dba_indexes
    where index_name = 'PK_ACK_ID'
    ;



    OWNER INDEX_NAME INDEX_TYPE TABLESPACE_NAME
    ------ --------- ----------- ---------
    DATA PK_ACK_ID NORMAL INDX



    Even I executed the query:
    Alter Index DATA.PK_ACK_ID Rebuild Tablespace INDX_NEW

    But got the same error

    ORA-01418: specified index does not exist

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Okay... here is your action plan.
    Code:
    -- run query below
    select  owner,
            constraint_name,
            constraint_type,
            table_name
    from    dba_constraints
    where   constraint_name = 'PK_ACK_ID'
    ;
    --
    -- Can you see your "index" there?
    -- If YES then
    --    you forgot to tell us the index was a PK
    --    drop PK by running query below
    -- else
    --    post results
    -- end if;
    --
    alter table DATA.ACKNOWLEDGEMENT_TAB 
        drop constraint PK_ACK_ID
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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