Re: Export weirdness

In article <8knclf$hhl$1_at_nnrp1.deja.com>,   kal121_at_my-deja.com wrote:
> Hello,
>
> I did an export from one database to another, so now the schemas,
 data,
> etc is identical on both databases.
>
> However, when I do a describe on the database that I imported to, the
> primary key field shows up as null. Yes, the primary key does exist!
> It's just not showing up:
>
> Exported from:
> SQL> desc table1
> Name Null? Type
> ---------------------------------- -------- -------------
> SITE_ID NOT NULL NUMBER(38)
>
> Imported to (primary key DOES exist):
> SQL> desc table1
> Name Null? Type
> ---------------------------------- ------- --------------
> SITE_ID NUMBER(38)
>
> Wonder why this is???
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Connect internal through svrmgrl and run the $ORACLE_HOME/rdbms/admin/utlexcpt.sql script. This will create the EXCEPTIONS table. Then connect as the user who owns table1 and attempt to enable the primary key:

SQL> alter table table1 enable primary key exceptions into sys.exceptions;

If there is no duplicate data in the table the pk constraint should be enabled without error. Should the process error out the rowids of the offending records will be found in the EXCEPTIONS table. Selecting the rows by their rowid's will tell you which keys are duplicated.

The only two reasons I can see for this happening is that the PK constraint never was enabled by imp, or the data is such that keys are duplicated and the constraint could not be enabled. However, this does not mean that there aren't other reasons for this behavior; these are the only two I am aware of at this time.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.