Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unix scripts to extract DDL from imp show=y
Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3B0BC935.7DCE_at_yahoo.com>...
>
> Its useful for re-creating most (but not all) of a database. In any
> event, my preference is to run 'strings' on an 'exp rows=n' rather than
> the indexfile - it avoids all that nasty stuff with line's wrapping -
> each ddl is on a single (long) line.
Sigh. Just realized a big problem.
Unfortunately, most of my large indexes are PK indexes and, the worst part is that they are unnamed i.e. SYS_Cnnnnn.
As a result, they dont show up in the 'imp indexfile=' as CREATE INDEX statements. Doing a 'imp indexfile=ind.sql constraints=y' DOES make them show up as 'REM ALTER TABLE ... ADD PRIMARY KEY ....'.
But, when I do the following,
ALTER TABLE ... ADD PRIMARY KEY ... USING INDEX ... NOLOGGING PARALLEL
COMPRESS;
Since I was not sure which of the above 3 clauses was giving the
error, I tried elimination:
PARALLEL: gives me ORA-3001: unimplemented feature.
COMPRESS: gives me ORA-14071: invalid option for an index used to
enforce a constraint
NOLOGGING: OK, this works.
Needless to say, all these 3 options work fine if I pre-create the index using CREATE UNIQUE INDEX.
So, obviously, creating a PK index via the ALTER TABLE command has severe limitations as compared to pre-creating it using CREATE UNIQUE INDEX and then doing a ALTER TABLE ADD PRIMARY KEY which will simply use the existing index (right?)
Sigh. Question: How can I get my CREATE INDEX statements for my SYS_Cnnnn indexes into the indexfile?
Once I get past this step and pre-create the indexes, I am assuming the I can run my current indexfile and Oracle will simply ignore the USING INDEX clause and use the pre-existing index, right?
Thanks. Received on Fri May 25 2001 - 10:04:14 CDT