| ORA-02270 during Import [message #464150] |
Tue, 06 July 2010 08:27  |
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 #464268 is a reply to message #464160] |
Wed, 07 July 2010 03:34  |
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 #464371 is a reply to message #464150] |
Wed, 07 July 2010 10:37 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
