Home » RDBMS Server » Server Utilities » Primary keys are not exporting when export using EXP command (Oracle 10g)
Primary keys are not exporting when export using EXP command [message #537216] Tue, 27 December 2011 05:29 Go to next message
irsathahamed
Messages: 12
Registered: April 2008
Location: UAE
Junior Member
Hi Everyone,

I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.

Kindly help me how will i take backup including with primary keys?

Thanks for your help.

Re: Primary keys are not exporting when export using EXP command [message #537220 is a reply to message #537216] Tue, 27 December 2011 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have taken database backup using exp command

No you took DATA backup not DATABASE one.

Quote:
I try to import in other pc the foreign keys are not imported

Then you didn't do it properly.

Quote:
Kindly help me how will i take backup including with primary keys?

You FIRST have to show us what you did and got.

Please read OraFAQ Forum Guide.

Regards
Michel
Re: Primary keys are not exporting when export using EXP command [message #537221 is a reply to message #537216] Tue, 27 December 2011 05:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Instead of explaining what you did, post the session/log.
All keys are exported by default. Either you asked for it or your session was not consistent.
Re: Primary keys are not exporting when export using EXP command [message #537225 is a reply to message #537216] Tue, 27 December 2011 05:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think your problem is to do with foreign key constraints.You are probably trying to import a child table without its parent table, for instance, scott.emp but not scott.dept.
Re: Primary keys are not exporting when export using EXP command [message #537227 is a reply to message #537225] Tue, 27 December 2011 05:49 Go to previous messageGo to next message
irsathahamed
Messages: 12
Registered: April 2008
Location: UAE
Junior Member
Here please find what I did..

C:\>exp

Export: Release 9.0.1.5.1 - Production on Tue Dec 27 15:12:01 2011

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


Username: system/system

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 > 4096

Export file: EXPDAT.DMP > d:\ims_27122011.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes > y

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > y

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
User to be exported: (RETURN to quit) > ims

User to be exported: (RETURN to quit) >

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user IMS
. exporting object type definitions for user IMS
About to export IMS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export IMS's tables via Conventional Path ...
. . exporting table COMPANY_DETAILS 1 rows exported
. . exporting table CURRENT_STOCK 0 rows exported
. . exporting table ITEM_MASTER
EXP-00003: no storage definition found for segment(4, 371)
. . exporting table MRN_DETAILS 0 rows exported
. . exporting table MRN_MASTER 0 rows exported
. . exporting table OPENING_STOCK_DETAILS 0 rows exported
. . exporting table OPENING_STOCK_MASTER 0 rows exported
. . exporting table PARAMETERS 1 rows exported
. . exporting table REQUEST_DETAILS 2 rows exported
. . exporting table REQUEST_MASTER 1 rows exported
. . exporting table STOCK_OUT_DETAILS 0 rows exported
. . exporting table STOCK_OUT_MASTER 0 rows exported
. . exporting table SUGGESTED_COMPANY 0 rows exported
. . exporting table SUPPLIER 0 rows exported
. . exporting table TECHNICAL_CONDITION 1 rows exported
. . exporting table TECHNICAL_STATUS_UPDATE 2 rows exported
. . exporting table WAREHOUSE_MASTER 5 rows exported
. 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 materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

Then I imported in other pc..




C:\>imp system/system fromuser=ims touser=temp file=d:\ims_27122011.dmp

Import: Release 9.0.1.5.1 - Production on Tue Dec 27 15:21:25 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.00.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing IMS's objects into TEMP
. . importing table "COMPANY_DETAILS" 1 rows imported
. . importing table "CURRENT_STOCK" 0 rows imported
. . importing table "MRN_DETAILS" 0 rows imported
. . importing table "MRN_MASTER" 0 rows imported
. . importing table "OPENING_STOCK_DETAILS" 0 rows imported
. . importing table "OPENING_STOCK_MASTER" 0 rows imported
. . importing table "PARAMETERS" 1 rows imported
. . importing table "REQUEST_DETAILS" 2 rows imported
. . importing table "REQUEST_MASTER" 1 rows imported
. . importing table "STOCK_OUT_DETAILS" 0 rows imported
. . importing table "STOCK_OUT_MASTER" 0 rows imported
. . importing table "SUGGESTED_COMPANY" 0 rows imported
. . importing table "SUPPLIER" 0 rows imported
. . importing table "TECHNICAL_CONDITION" 1 rows imported
. . importing table "TECHNICAL_STATUS_UPDATE" 2 rows imported
. . importing table "WAREHOUSE_MASTER" 5 rows imported
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "MRN_DETAILS" ADD FOREIGN KEY ("BAR_CODE", "ITEM_CODE") REFEREN"
"CES "ITEM_MASTER" ("BARCODE", "ITEM_CODE") ENABLE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "STOCK_OUT_DETAILS" ADD FOREIGN KEY ("BAR_CODE", "ITEM_CODE") R"
"EFERENCES "ITEM_MASTER" ("BARCODE", "ITEM_CODE") ENABLE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "OPENING_STOCK_DETAILS" ADD FOREIGN KEY ("BAR_CODE", "ITEM_CODE"
"") REFERENCES "ITEM_MASTER" ("BARCODE", "ITEM_CODE") ENABLE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:
"ALTER TABLE "CURRENT_STOCK" ADD FOREIGN KEY ("BARCODE", "ITEM_CODE") REFERE"
"NCES "ITEM_MASTER" ("BARCODE", "ITEM_CODE") ENABLE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
About to enable constraints...
Import terminated successfully with warnings.


Please help me.
...
Re: Primary keys are not exporting when export using EXP command [message #537232 is a reply to message #537227] Tue, 27 December 2011 05:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
. . exporting table ITEM_MASTER
EXP-00003: no storage definition found for segment(4, 371)

There's your problem: the parent table didn't export.

You should not use the 9i export/import utilities against a 10g database.
Re: Primary keys are not exporting when export using EXP command [message #537233 is a reply to message #537232] Tue, 27 December 2011 06:03 Go to previous messageGo to next message
irsathahamed
Messages: 12
Registered: April 2008
Location: UAE
Junior Member
Thank you for your reply Mr. John Watson,

Then what utilities i have to use to export/import.
Re: Primary keys are not exporting when export using EXP command [message #537234 is a reply to message #537233] Tue, 27 December 2011 06:05 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Er... how about the 10g export/import utilities?
Previous Topic: Downgrade from Oracle11g to Oracle9i via exp/imp
Next Topic: Backup determination
Goto Forum:
  


Current Time: Thu Mar 28 17:30:32 CDT 2024