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
On 25 May 2001 08:04:14 -0700, onlyforposting_at_yahoo.com (Vikas Agnihotri) wrote:
>
>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.
Ayup! I've heard that if you set PARALLEL DEGREE on the table itself, the ADD PRIMARY KEY USING INDEX will do a parallel index creation. I have not been able to make that work in any version up to 8i and haven't tried yet in that one.
>
>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?)
Yup!
>
>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?
>
I think you're trying to do too much through exp/imp. You can use SQL itself (as I'm sure you are aware) to create the "CREATE INDEX" strings yourself into a spool file. And the same goes for the ALTER TABLE ADD PRIMARY KEY. From the dictionary of the source db. Edit the spool file (or generate it already the right way) to match the target db.
Wouldn't this be a better option rather than fiddle with exp/imp variations? Just a suggestion.
Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html
Received on Fri May 25 2001 - 10:45:42 CDT