Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unix scripts to extract DDL from imp show=y

Re: Unix scripts to extract DDL from imp show=y

From: Vikas Agnihotri <onlyforposting_at_yahoo.com>
Date: 25 May 2001 08:04:14 -0700
Message-ID: <77e87b58.0105250704.447685a4@posting.google.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US