Index missing after exp with TRANSPORT

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: 10 Oct 2006 07:49:58 -0700
Message-ID: <1160491798.190946.160820@i42g2000cwa.googlegroups.com>

RDBMS: Oracle 9i Release 9.2.0.6.0
O/S: AIX 5.2 (64-bit)

PROBLEM:
Certain indexes/primary keys go missing after an exp/imp using transportable tablespace. They still exist, but a SELECT command cannot locate them and the index is not used for any reports that are generated which use that table.

BACKGROUND:
We have a monthly procedure that exports the database contents from a production server and then imports that data into a historical database environment so we can perform monthly sales reporting and analysis without impacting the production server.

Twice in the last 5-6 data transfers, we have run into a problem where the performance on some of the reports was extremely poor. Reports that took seconds or minutes to complete would suddenly be taking hours.

An analysis showed that one particular table (APTPYH) was supposed to have an unnamed primary key (SYS_C00139087) and two unique indexes (PYH_KEY1, PYH_KEY2). In the live environment, these keys existed fine. However, after the export and import using TRANSPORT_TABLESPACE=Y, the primary key and the first index appeared to have been missing when you issue a SELECT statement, as below:

SQL> select index_name from all_indexes where table_name='APTPYH';

INDEX_NAME

PYH_KEY2 When we issue a SQL command to recreate the missing indexes, we get one of these messages depending on if it was the primary key or the index we are trying to create:

ORA-02260: table can have only one primary key
ORA-00955: name is already used by an existing object

What we end up having to do is drop the indexes first (and any constraints), then recreate anything that was deleted so that it shows properly. Once they are recreated, we see the following:

SQL> select index_name from all_indexes where table_name='APTPYH';

INDEX_NAME

SYS_C00139087
PYH_KEY1
PYH_KEY2

In both cases where the indexes went missing, it was the same table that was affected. In one case, both primary key and index PYH_KEY1 was missing. In the second occurrence, only the primary key was missing.

I will be reporting this to Oracle on Metalink shortly, but their site appeared to be down this morning, so figured I'd post it here to see if anyone else has run into this same scenario. Would like to know anyone's thoughts on this.

Thx.

Steve Received on Tue Oct 10 2006 - 09:49:58 CDT

Index missing after exp with TRANSPORT_TABLESPACE=Y