No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48209
Total active users1645
Act. users last 24h6
Act. users last hour0
Registered user hits last week186
Registered user hits last month812
Go up

Error at the time of schema import.
Next thread: statistics pending state
Prev thread: 12 c PDB CDB - Unit based pricing challenge

Message Score Author Date
Hello experts. I am exporting schema(Refreshing...... tayyab shahzad Mar 13, 2018, 02:40
If i drop sequences in test and import from prod w...... tayyab shahzad Mar 13, 2018, 04:15
Hello Tayyab (first a remark: you are not "<i>e...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Mar 13, 2018, 10:40
Hi Bruno, Thank you for the detailed response. ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts tayyab shahzad Mar 13, 2018, 19:46

Follow up by mail Click here


Subject: Error at the time of schema import.
Author: tayyab shahzad, Canada
Date: Mar 13, 2018, 02:40, 100 days ago
Os info: AIX
Oracle info: 12.1
Message: Hello experts.

I am exporting schema(Refreshing data) from prod to test. with following par file.

remap_schema=P_JIRA_ADMIN:D_JIRA_ADMN
TABLE_EXISTS_ACTION=replace
directory=EXPORT_FILES
dumpfile=expdp_JIRA_inc_813546.dmp
logfile=impdp_JIRA_inc_813546.log

And i am getting the following error.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"D_JIRA_ADMIN"."PK_SEQUENCE_VALUE_ITEM" failed to create with error:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Failing sql is:
CREATE UNIQUE INDEX "D_JIRA_ADMIN"."PK_SEQUENCE_VALUE_ITEM" ON "D_JIRA_ADMIN"."SEQUENCE_VALUE_ITEM" ("SEQ_NAME") PCTFREE 10 INITRANS 2AGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLELL_FLASH_CACHE DEFAULT) TABLESP
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT:"D_JIRA_ADMIN"."PK_SEQUENCE_VALUE_ITEM" failed to create with error:
ORA-02437: cannot validate (D_JIRA_ADMIN.PK_SEQUENCE_VALUE_ITEM) - primary key violated
Failing sql is:
ALTER TABLE "D_JIRA_ADMIN"."SEQUENCE_VALUE_ITEM" ADD CONSTRAINT "PK_SEQUENCE_VALUE_ITEM" PRIMARY KEY ("SEQ_NAME") USING INDEX PCTFREE ANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS


Can you please let me know how to fix this issue. ?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error at the time of schema import.
Author: tayyab shahzad, Canada
Date: Mar 13, 2018, 04:15, 100 days ago
Message: If i drop sequences in test and import from prod with impdp ? do you think this will work ?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error at the time of schema import.
Author: Bruno Vroman, Belgium
Date: Mar 13, 2018, 10:40, 100 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hello Tayyab

(first a remark: you are not "exporting" with the given parfile but "importing")

Are you sure that everything is OK in the source database? (maybe the constraint has been enabled with "novalidate" but duplicate data exists as well in source)

But the issue is probably hidden behind the "table_exists_action"... your schema is not empty when you do the import.
Are there active triggers that are fired when you import?

I suggest that you DROP the objects (at least tables and sequences, but everything is better) in schema D_JIRA_ADMIN before running the impdp session.

Before this you might look at the data to understand what happens. For example you might run
WITH duplicates AS

( SELECT seq_name, COUNT(*) nb
FROM d_jira_admin.sequence_value_item
GROUP BY seq_name
HAVING COUNT(*) > 1
ORDER BY nb DESC
)
SELECT *
FROM duplicates
WHERE rownum <= 20
;
Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error at the time of schema import.
Author: tayyab shahzad, Canada
Date: Mar 13, 2018, 19:46, 100 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Bruno,

Thank you for the detailed response.

yes, the error was due to duplicate sequence number.
So i dropped the sequences and run the import again and it worked like charm.

Thanks again for the effort.

Tayyab.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here