How to use INDEXFILE param in Import
552758Jan 19 2007 — edited Jan 30 2007ok, this is my first time trying to use this option. Oracle9i. Here's what I am doing.
I have in my Import .bat file:
imp user1/user1@db2 FILE=D:\exp1230.dmp LOG=D:\IMP.log FROMUSER=USER1 TOUSER=USER1 BUFFER=100000 IGNORE=Y INDEXFILE=D:\indexfile.sql
I run this and it creates the indexfile.sql with all the create table and create index statements. I modify this file and remove all the REM's. The tablespaces I dumped are different than what I want in the new db so I change the tablespace of the indexes to INDX and the tables to DATA. I run the script and all seems to work just fine. Now I need to go back and run the Import again to get the data in..right? I think this is where I'm missing something. Well, I run the Import again and get lots of ORA-02291 Integrity constraint violated- parent key not found errors. Which I don't understand because I can see that all the keys were created. Then I think I should have used the INDEXES=N param. I start over and remove the INDEXFILE param and add the INDEXES=N param but end up getting tons of ORA-00001 unique constraint violations.
I'd like to use this option for two reasons. 1- to speed up the Import, and 2- to move the indexes to their own INDX tablespace. Currently they are on the same DATA tablespace as the tables.
Any suggestions would be greatly appreciated!