Issue while moving INDEX from one tablespace to other tablespace

[vishnu]

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.

[PAVB]

Please run query below and post results...

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 (#)

[LKBrwn_DBA]

Just a thought, did you try:

Alter Index DATA.PK_ACK_ID Rebuild Tablespace INDX_NEW?

[vishnu]

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

[PAVB]

Okay... here is your action plan.

-- 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
;