Very Computer

It seems to me that the 'imp' parameter 'show=y' is not working correctly
on my 8i database: it is not showing me the DDL for referential integrity
constraints (neither primary nor foreign keys are showing up).

Versions:
-------------------------
Import: Release 8.1.5.0.0 - Production
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
SunOS ls 5.6 Generic_105181-13 sun4u sparc SUNW,Ultra-250

Steps to recreate the "bug"
--------------------------
1. Starting with a new user in a new tablespace, create a couple of
   tables that include primary and foreign keys

2. Do a user mode, no rows, export (exp abe/XXX owner=abe rows=n)

3. Do a full 'show=y' import (imp abe/XXX show=y full=y file=expdat.dmp)

The ugly DDL output by step three does not contain the primary key nor
foreign key DDL. I have used (and still use) the above steps in 8.0.5
and I get the referential integrity constraint DDL just fine.

Also, I can do a regular import (show=n) of the dump file into another
8i server and the referential integrity constraints are present. So I
feel that exp is dumping them OK, and that imp imports them OK, but
that 'show=y' seems broken.

Is this a bug, or am I doing something wrong?

Details ========================================================

===== Test DDL =====

CREATE TABLE parent (
       parent_pk            INTEGER NOT NULL,
       parent_data          VARCHAR2(20) NULL,
       PRIMARY KEY (parent_pk)
);

COMMENT ON TABLE parent IS 'test parent';

CREATE TABLE child (
       child_pk             INTEGER NOT NULL,
       parent_pk            INTEGER NOT NULL,
       child_data           VARCHAR2(20) NULL,
       PRIMARY KEY (child_pk)
);

COMMENT ON TABLE child IS 'test child';
CREATE INDEX XIF1child ON child
(
       parent_pk                      ASC
);

ALTER TABLE child
       ADD  ( FOREIGN KEY (parent_pk)
                             REFERENCES parent ) ;

===== Dump =====

$  exp abe/XXX owner=abe rows=n                                                      

Export: Release 8.1.5.0.0 - Production on Fri Dec 10 14:28:53 1999

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
Note: table data (rows) will not be exported
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ABE
. exporting object type definitions for user ABE
About to export ABE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ABE's tables via Conventional Path ...
. . exporting table                          CHILD
. . exporting table                         PARENT
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
Export terminated successfully without warnings.
$

===== Failed show=y import =====

$ imp abe/XXX show=y full=y file=expdat.dmp                                        

Import: Release 8.1.5.0.0 - Production on Fri Dec 10 14:30:11 1999

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

Export file created by EXPORT:V08.01.05 via conventional path
import done in US7ASCII character set and US7ASCII NCHAR character set
. importing ABE's objects into ABE
 "CREATE TABLE "CHILD" ("CHILD_PK" NUMBER(*,0) NOT NULL ENABLE, "PARENT_PK" N"
 "UMBER(*,0) NOT NULL ENABLE, "CHILD_DATA" VARCHAR2(20))  PCTFREE 10 PCTUSED "
 "40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 81920 NEXT 65536 MINEXTE"
 "NTS 1 MAXEXTENTS 256 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
 "L DEFAULT) TABLESPACE "ABE""
 "CREATE INDEX "XIF1CHILD" ON "CHILD" ("PARENT_PK" )  PCTFREE 10 INITRANS 2 M"
 "AXTRANS 255 STORAGE(INITIAL 81920 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 256 PC"
 "TINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ""
 "ABE" LOGGING"
 "COMMENT ON TABLE "CHILD" IS  'test child'"
 "CREATE TABLE "PARENT" ("PARENT_PK" NUMBER(*,0) NOT NULL ENABLE, "PARENT_DAT"
 "A" VARCHAR2(20))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STO"
 "RAGE(INITIAL 81920 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0 FRE"
 "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ABE""
 "COMMENT ON TABLE "PARENT" IS  'test parent'"
Import terminated successfully without warnings.
$

===== Constraints exist after regular import on another server ==============

SQL> select
 2       table_name
 3       constraint_name,
 4       constraint_type
 5  from
 6       user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
CHILD                          SYS_C0012297                   C
CHILD                          SYS_C0012298                   C
CHILD                          SYS_C0012299                   P
CHILD                          SYS_C0012302                   R
PARENT                         SYS_C0012300                   C
PARENT                         SYS_C0012301                   P

--
Dave Abercrombie          (510) 653-1204          122 15.23' W