Re: Import INDEXFILE option doesn't work.

"Charles J. Fisher" <cfisher_at_rhadmin.org> wrote in message news:<Pine.LNX.4.33.0110220839520.8222-100000_at_galt.rhadmin.org>...
> Because my import was running so slow, I took some advice here and
> specified INDEXES=n, and got the data into the system faster.
>
> Now, I would like to recreate the indexes.
>
> I decided to try the INDEXFILE option, using the method outlined by Jason
> Couchman in his 8i OCP book on page 800.
>
> The problem is that the SQL file generated by the INDEXFILE option is just
> a bunch of REMed CREATE/ALTER TABLE statements.
>
> I read in an Oracle 7 book on exp/imp that interspersed in these REMed
> directives should be uncommented INDEX directives; why don't I see them?
>
> ---------------------------------------------------------------------------
> / Charles J. Fisher | "Those who do not understand UNIX /
> / cfisher_at_rhadmin.org | are condemned to reinvent it, /
> / http://rhadmin.org:81 | poorly." -- Henry Spencer /
> ---------------------------------------------------------------------------

Did you export the indexes to begin with? The default is indexes=y on the exp but if you coded indexes=n then that is why you do not see them in the indexfile. The indexfile option of imp should write the create index statements to a file providing you did not leave the indexes=n option on the imp where you specified indexfile. I think that will create a source file of commented out create and alter table statements like you have.

Also I do not think that the indexes=n option on imp will stop Oracle from creating indexes used to support PK and UK constraints. To do that I think you need to include the constraints=n option also. You may want to look at the dba_indexes view to see what you have in the reloaded or new instance.

If you have lost your index definitions you can retrive them from an older export. Just import it with the rows=n ignore=y option to prevent bringing data. If your index defitions are your exp file then this is easier and quicker than editing the text to create the 'create index' scripts unless you are relocating them or changing their storage parameters.

I make it a rule to always generate my source code before starting with data movement. It prevents problems like this.

• Mark D Powell --