Re: Index missing after exp with TRANSPORT_TABLESPACE=Y

From: steven_nospam at Yahoo! Canada <steven_nospam_at_yahoo.ca>
Date: 12 Oct 2006 07:03:12 -0700
Message-ID: <1160661792.449610.16800@c28g2000cwb.googlegroups.com>

Frank van Bortel wrote:
> Perhaps stating the obvious, but are the indexes actually
> in the tablespace you are ex/transporting?
> Could not find this in the thread so far.
> --
> Regards,
> Frank van Bortel

Yes. indexes were there in the source database. Here are some of the details I sent to Oracle to help them identify the problem. I included the steps we do for the data transfer between source and destination SIDs, the check of the indexes, and the commands we use to fix the problem.

STEPS PERFORMED FOR DATABASE COPY (L02 to E02):

---

All users are logged off the SOURCE and DESTINATION databases when these steps are occurring.

1. Locate the DATA tablespace in the destination database and drop its contents. This will make room for the fresh data now being brought in. We drop the DATA tablespace and its contents and then delete the associated data files.
2. To get the list of data files associated with the DATA tablespace: SELECT NAME FROM V$DATAFILE_HEADER WHERE TABLESPACE_NAME=ESSMPL02DATA;
3. To drop the DATA tablespace: ALTER TABLESPACE ESSMPL02DATA OFFLINE; DROP TABLESPACE ESSMPL02DATA INCLUDING CONTENTS;
4. Drop the users that had ESSMPL02DATA as their default tablespace: DROP USER ESSMPL02 CASCADE;
5. Delete the affected data files using UNIX rm command.
6. Locate the DATA tablespace in the source database. Make a list of its users and data files. Place the data tablespace in read only mode. Copy the data files from source to destination directories. Create the missing users in the destination database (associated to system tablespace for now) Grant create session/table/view/synonym as required by our users. Issue the export command with transportable tablespaces, as below:

   exp userid=\"sys/change_on_install as sysdba\"

       FILE=/orabackup/ESSMPL02DATA.dmp
       TRANSPORT_TABLESPACE=y TABLESPACES=ESSMPL02DATA

3) Issue the import command to bring the "just-exported" data into destination database

   imp userid=\"sys/change_on_install as sysdba\"

       FILE=/orabackup/ESSMPL02DATA.dmp
       TRANSPORT_TABLESPACE=y TABLESPACES=ESSMPL02DATA
       DATAFILES=/e02/datafile1,/e02/datafile2,/e02/datafile3,etc...

4) Alter the users default tablespace and temporary tablespace so they are using

   ESSMPL02DATA and ESTELLIVTEMP instead of SYSTEM.    Adjust the users quotas.

RESULTS FROM TRANSPORT_SET_VIOLATIONS:

---

SQL> execute sys.dbms_tts.transport_set_check('ESSMPL02DATA',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

(NOTE: Under Oracle 8i, we used to get errors about tables not self-contained due to descending indexes, but we no longer have this issue in Oracle 9i)

AFFECTED TABLE:

---

The table APTPYH_REC should have an unnamed primary key and two unique indexes.
In the source database, these show correctly. In the destination after the import, only one of the indexes can be selected...)

SOURCE DATABASE:
select INDEX_NAME, INDEX_TYPE, TABLE_TYPE from ALL_INDEXES where table_name='APTPYH_REC';

INDEX_NAME                     INDEX_TYPE                  TABLE
------------------------------ --------------------------- -----
SYS_C00139087                  NORMAL                      TABLE
PYH_KEY1                       NORMAL                      TABLE
PYH_KEY2                       NORMAL                      TABLE

DESTINATION DATABASE:
select INDEX_NAME, INDEX_TYPE, TABLE_TYPE from ALL_INDEXES where table_name='APTPYH_REC';

INDEX_NAME                     INDEX_TYPE                  TABLE
------------------------------ --------------------------- -----
PYH_KEY2                       NORMAL                      TABLE

When we try to create the primary key:

SQL> ALTER TABLE APTPYH_REC ADD PRIMARY KEY   2 (PYH_VEN_NO,PYH_REF_DT,PYH_BRH,PYH_REF_CD,PYH_REF_NO); ALTER TABLE APTPYH_REC ADD PRIMARY KEY

                           *

ERROR at line 1:
ORA-02260: table can have only one primary key

If we try to drop the primary key:

SQL> ALTER TABLE APTPYH_REC DROP PRIMARY KEY; ALTER TABLE APTPYH_REC DROP PRIMARY KEY
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

We must then issue the following set of SQL commands to get the primary key and missing unique index to be displayed when we issue a select statement:

ALTER TABLE APTPYD_REC DROP CONSTRAINT APTPYDAPTPYH0; ALTER TABLE APTPYH_REC DROP PRIMARY KEY; ALTER TABLE APTPYH_REC ADD PRIMARY KEY
  (PYH_VEN_NO,PYH_REF_DT,PYH_BRH,PYH_REF_CD,PYH_REF_NO); ALTER TABLE APTPYD_REC ADD CONSTRAINT APTPYDAPTPYH0   FOREIGN KEY (PYD_VEN_NO, PYD_REF_DT, PYD_BRH, PYD_REF_CD, PYD_REF_NO)   REFERENCES APTPYH_REC
  (PYH_VEN_NO,PYH_REF_DT,PYH_BRH,PYH_REF_CD,PYH_REF_NO); CREATE UNIQUE INDEX PYH_KEY1 ON APTPYH_REC   (PYH_VEN_NO, PYH_REF_CDT, PYH_BRH, PYH_REF_CD, PYH_REF_NO, PYH_LOADED_ITM); CREATE UNIQUE INDEX PYH_KEY2 ON APTPYH_REC   (PYH_REF_CD, PYH_REF_NO, PYH_VEN_NO, PYH_REF_DT, PYH_BRH); Received on Thu Oct 12 2006 - 09:03:12 CDT