Re: Need Help: Question on Export/Import

[Quoted] [Quoted] It seems weird that the data may contain duplicate primary key values because
it was just exported from an Oracle DB with same version! The data was okay [Quoted] in the original database.

I think the problem may be caused by the import process, which, somehow convert the data to fit the characterset of the new DB. I found that there was
[Quoted] a line saying:

    Export done in WE8ISO8859P1 character set and ZHT16BIG5 NCHAR character set
[Quoted] server uses ZHT16BIG5 character set (possible charset conversion)

and, the data imported were simply converted to strange characters like "??", "???",
which caused the Primary key validation error. Then the question is, why there was charset conversion when the 2 database are the same?

Any clue?

Thanks

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:401fe3d5$0$32650$626a54ce_at_news.free.fr...
>
> "M" <someone_at_microsoft.com> a écrit dans le message de
news:bvomhf$212r$1_at_news.hgc.com.hk...
> > Hi All,
> >
> > I'm a Oracle Developer which occassionally need to perform some simple
DBA
> > work.
> > Recently I'm trying to setup another PC with Oracle 8.1.5 and needed to
> > export
> > a schema in the old DB to the new one. However, the data imported
cannot
> > seems
> > not correctly, specifically, those double-byte characters.
> >
> > My export script is:
> > exp file=c:\db_data.dmp userid=system/manager owner=busapp
> >
> > where busapp is the schema containing all the data.
> >
> > This export script runs well and data are exported with no warnings.
> > The NLS_LANG of the old DB is AMERICAN_AMERICA.ZHT16BIG5.
> >
> > When I import this to the new DB, it has error/warning as follows:
> >
> > IMP-00017: following statement failed with ORACLE error 2437:
> > "ALTER TABLE "MY_TAB" ADD CONSTRAINT "TAB_PK" PRIMARY KEY ("BUS_N"
> > "O") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIA"
> > "L 802816 NEXT 147456 MINEXTENTS 1 MAXEXTENTS 300 PCTINCREASE 1
FREELISTS 1
> > "
> > "FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
> > NOVALIDAT"
> > "E"
> >
> > Import terminated successfully with warnings.
> >
> > Can anyone guide me to found a solution? As I mentioned before, I'm not
the
> > DBA,
> > so please let me know the concept towards solving the problem.
> >
> > Thanks very much.
> > Mandy
> >
> >
> >
>
> ORA-02437 cannot validate (string.string) - primary key violated
> Cause: An attempt was made to validate a primary key with duplicate values
or null values.
> Action: Remove the duplicates and null values before enabling a primary
key.
>
> So seems you have duplicate values in bus_no columns.
>
> btw, you should *never* create your object in the tablespace SYSTEM.
>
> Regards
> Michel Cadot
>
>
Received on Wed Feb 04 2004 - 17:32:11 CET