Home » RDBMS Server » Server Utilities » ORA-02270 during Import (Oracle 9.2.0.6.0 on HP-UX)
ORA-02270 during Import [message #464150] Tue, 06 July 2010 08:27 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

I was cloning a schema user1 as user2 in the same database

user1 had quota on 2 tablespaces user1_data and user1_index

I created user with name as user2

I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege)

Now exported user1 schema as follows
exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y


then imported in in user2 schema as follows
imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y


during import i encountered following errors for so many constraints
"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
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:


I found that the it happened as the primary key of table1 was not created for which error was logged in the log file
. . importing table             "table1"      19441 rows imported
IMP-00015: following statement failed because the object already exists:
 "ALTER TABLE "table1" ADD  CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
 "ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
 "REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table  "table5"          0 rows imported


However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema
Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import

Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?

I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import

I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same

There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export

Also I googled it, read in this forum as well but the resaons mentioned aren't applicable in this case

Please suggest

Thanks and Regards,
Chetana
Re: ORA-02270 during Import [message #464160 is a reply to message #464150] Tue, 06 July 2010 08:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems issue with dependencies.
First import with rows=n (create all table / index and no data)
Then import with ignore=y ( import data, ignore=y will ignore
the error message for presence of object)
Re: ORA-02270 during Import [message #464268 is a reply to message #464160] Wed, 07 July 2010 03:34 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hello Mahesh

Thanks for your quick reply on this

There are 2 steps in your reply

1) import with rows = n
2) import with rows=y and ignore = y


to start with, I dropped the user User3 and started with step 1 above
imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user3_schema_imp.log fromuser=user1 touser=user3 rows=n constraints=y indexes=y statistics=none recordlength=65535 grants=y


During Step 1 only I got errors like following


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing User1's objects into User3
IMP-00015: following statement failed because the object already exists:
 "ALTER TABLE "D1_CONTACTS_DATA" ADD  CONSTRAINT "T1_PK24" PRIMARY KEY ("LBR_"
 "ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
 "REELISTS 1 FREELIST GROUPS 1) TABLESPACE "User1_INDEX" NOLOGGING ENABLE "
IMP-00015: following statement failed because the object already exists:
 "ALTER TABLE "D1_CONTRACT_DATA" ADD  CONSTRAINT "T1_PK38" PRIMARY KEY ("CONT"
 "RACT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65"
 "536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "User1_DATA" LOGGING ENABL"
 "E "
IMP-00015: following statement failed because the object already exists:
 "ALTER TABLE "D1_CUSTOMER_ACCOUNTS" ADD  CONSTRAINT "T1_PK76" PRIMARY KEY (""
 "CUN_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 655"
 "36 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "User1_INDEX" NOLOGGING ENA"
 "BLE "



I have not yet completed Step 2

I created user3 just before this import then what could be the reason I am getting 'object exists error'?

Is it because of the tablespaces "User1_DATA" and "User1_INDEX" in the dump file? (as we can see in the log file)
If answer to this "yes" then why it is not happening for other PK, UK indexs of user1 which too fall in "User1_DATA" and "User_INDEX" tablespaces

select owner,constraint_name,constraint_type,table_name,last_change,index_owner,index_name from dba_constraints where constraint_name in('T1_PK24','T1_PK76');

OWNER	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME	LAST_CHANGE	INDEX_OWNER	INDEX_NAME
USER1	T1_PK24	P	D1_CONTACTS_DATA	31/10/2009 17:27	USER1	D1_CTT_PK
USER1	T1_PK76	P	D1_CUSTOMER_ACCOUNTS	31/10/2009 17:27	USER1	D1_CUN_PK
USER2	T1_PK24	P	D1_CONTACTS_DATA	06/07/2010 09:52	USER2	D1_CTT_PK
USER2	T1_PK76	P	D1_CUSTOMER_ACCOUNTS	06/07/2010 09:52	USER2	D1_CUN_PK


select owner,index_name,table_owner,table_name,tablespace_name from dba_indexes where index_name in('D1_CTT_PK','D1_CUN_PK');

OWNER	INDEX_NAME	TABLE_OWNER	TABLE_NAME	TABLESPACE_NAME
USER1	D1_CUN_PK	USER1	D1_CUSTOMER_ACCOUNTS	USER1_INDEX
USER1	D1_CTT_PK	USER1	D1_CONTACTS_DATA	USER1_INDEX
USER2	D1_CTT_PK	USER2	D1_CONTACTS_DATA	USER2_DATA
USER2	D1_CUN_PK	USER2	D1_CUSTOMER_ACCOUNTS	USER2_DATA
USER3	D1_CTT_PK	USER3	D1_CONTACTS_DATA	USER3_DATA
USER3	D1_CUN_PK	USER3	D1_CUSTOMER_ACCOUNTS	USER3_DATA



Thus Index (indices) was/were created for User3 but not the PK to which it belonged to. (I checked 2 of them which produced errors)

Also I checked that the indexes 'D1_CTT_PK','D1_CUN_PK' were created in User3 schema after import only

Please find the complete log file attached for Step1 of the import

Thanks and Regards,
Chetana
Re: ORA-02270 during Import [message #464283 is a reply to message #464268] Wed, 07 July 2010 04:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Bug# 3102140. Check metalink for details.
>>(did not grant him 'resource' role or unlimited tablespace privilege)
Seems you need either one of these or manually fix constraints.
Re: ORA-02270 during Import [message #464371 is a reply to message #464150] Wed, 07 July 2010 10:37 Go to previous message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Many Thanks Mahesh.

It is really helpful

I have read the metalink note and I will try the workaround mentioned in the note

Meanwhile I am trying to figure out if such error can be produced if souce schema has DDL in the following sequence:

create table T(n number not null)
Alter table T add constraint T_PK primary key(n) using index...

then this schema is cloned

I shall update the results here

Thanks and Regards,
Chetana



Previous Topic: please Help....5-1 Performing a Simple Schema Export Errors.
Next Topic: how to assign a numeric value gererated from sequence in a control file
Goto Forum:
  


Current Time: Fri Mar 29 07:55:38 CDT 2024