Re: imp-00015 for PK constraint

Ben wrote:
> Mark D Powell wrote:
> > Ben wrote:
> > > running 9.2.0.5 on AIX 5.3, LMT's
> > > I'm trying to duplicate a schema within an instance. I used OEM to
> > > create a user "like" the user/schema I want to duplicate. Then I
> > > revoked unlimited tablespace from that new user DP2 and gave it
> > > unlimited quota on a tablespace that I created for that user.
> > > When I run the imp using the following params I get an imp-00015 on
> > > certain primary key constraints. The odd thing is I don't get this for
> > > all pk constraints. How can I get around this? Just build the pk
> > > manually after the imp?
> > > Here are the params:
> > > fromuser=dp
> > > touser=dp2
> > > ignore=n
> > > full=n
> > > grants=y
> > > rows=y
> > > indexes=y
> > > commit=y
> > >
> > > Thanks for any help,
> >
> > Ben, we ran 9.2.0.5 on AIX 5.2 I do not remember encountering this
> > problem, but you should be able to extract the PK DDL off the original
> > tables and modify it to point to the new owner version of the table as
> > a work around solution.
> >
> > Check to see if the failing PK definitions have a common theme such as
> > system generated names. Finding the common thread will allow you to
> > identify problem objects in advance for future import activity.
> >
> > HTH -- Mark D Powell --
>
>
> Thanks for the reply Mark.
>
> Here are three of the specific PK failures.
>
> IMP-00015: following statement failed because the object already
> exists:
> "ALTER TABLE "CAUSAL_FACTORS_DAILY" ADD CONSTRAINT "CAUSAL_DAILY_PK"
> PRIMARY KEY
> ("FACTOR_ID") USING INDEX PCTFREE 10 INITRANS
> 2 MAXTRANS 255 STORAGE(INITIAL 49152 FREELISTS 1 FREELIST GROUPS 1)
> TABLESPACE "TS_DP_X" LOGGING ENABLE "
>
> IMP-00015: following statement failed because the object already
> exists:
> "ALTER TABLE "CAUSAL_FACTORS_MONTHLY" ADD CONSTRAINT
> "CAUSAL_MONTHLY_PK" PRIMARY KEY
> ("FACTOR_ID") USING INDEX PCTFREE 10 INITRANS 2
> MAXTRANS 255 STORAGE(INITIAL 49152 FREELISTS 1
> FREELIST GROUPS 1) TABLESPACE "TS_DP_X"
> LOGGING ENABLE "
>
> IMP-00015: following statement failed because the object already
> exists:
> "ALTER TABLE "CAUSAL_FACTORS_WEEKLY" ADD CONSTRAINT "CAUSAL_WEEKLY_PK"
> PRIMARY KEY
> ("FACTOR_ID") USING INDEX PCTFREE 10 INITRANS 2
> MAXTRANS 255 STORAGE(INITIAL 49152 FREELISTS 1
> FREELIST GROUPS 1) TABLESPACE "TS_DP_X" LOGGING
> ENABLE "
>
> I also ran an import to an indexfile and looked at the sql for these
> constraints. In the indexfile it has the new owner prefix ("DP2")
> before the table name but that doesn't show on these errors.
>
> Should I just manually recreate these after the import?
>
> Thanks,

I would say yes. The DDL is missing the owner as in alter table "owner"."table_name" that should be there. I would say this is a bug.

IMHO -- Mark D Powell -- Received on Wed Aug 09 2006 - 09:38:08 CDT