8i imp show=y not showing primary and foreign key constraints

From: Dave Abercrombie <abe_at_rahul.net>
Date: 10 Dec 1999 23:05:43 GMT
Message-ID: <82s108$1v5$1@samba.rahul.net>

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"

Starting with a new user in a new tablespace, create a couple of tables that include primary and foreign keys
Do a user mode, no rows, export (exp abe/XXX owner=abe rows=n)
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

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

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
abe_at_rahul.net                                      37 50.97' N

Received on Fri Dec 10 1999 - 17:05:43 CST