-
Getting Unique Constraints while copying from 1 DB to other.
I am trying to copy data from PRO to ACP for some testing purpose....
but am getting unique constraint even though there dosent exist any dupilcate records in PRO...
Because both ACP & PRO have PK set of columns same...i belive i should not get this error....i mean it dosent make sense...
PS below
Code:
ACP side
WW15:W_DBA>copy from wwciw_dba@ww04 to wwciw_dba@ww15 insert WWCIW_SAP.ytcurr using select * from wwciw_sap.ytcurr
FROM*********
TO*****
Array fetch/bind size is 1. (arraysize is 1)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
ERROR:
ORA-00001: unique constraint (WWCIW_SAP.SYS_C001519) violated
WW15:W_DBA>select * from dba_cons_columns where constraint_name='SYS_C001519';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ -------
WWCIW_SAP SYS_C001519 YTCURR KURST
WWCIW_SAP SYS_C001519 YTCURR FCURR
WWCIW_SAP SYS_C001519 YTCURR TCURR
WWCIW_SAP SYS_C001519 YTCURR GDATU
Code:
PRO Side
WW04:W_DBA>select * from dba_cons_columns where constraint_name='SYS_C001519';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------
WWCIW_SAP SYS_C001519 YTCURR KURST
WWCIW_SAP SYS_C001519 YTCURR FCURR
WWCIW_SAP SYS_C001519 YTCURR TCURR
WWCIW_SAP SYS_C001519 YTCURR GDATU
WW04:W_DBA> select
2 KURST,
3 FCURR,
4 TCURR,
5 GDATU
6 from wwciw_sap.ytcurr having count(*) > 1
7 group by
8 KURST,
9 FCURR,
10 TCURR,
11 GDATU;
no rows selected
Can any body throw some light??
am confused...
Thanks
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
If u are using any sequences better check this from metalink
ID:Note:125720.1
-
No sequences are not invloved here.
Thanks anyway
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
try this query
select
KURST,FCURR,TCURR,GDATU,count(1)
from wwciw_sap.ytcurr
group by
KURST,FCURR,TCURR,GDATU
having count(1) > 1;
-
I guess your WWCIW_SAP.ytcurr table in ACP side is not empty before you start copying. So some records with the same unique constraint values that you are trying to copy from the other table obviously allready exists in your destination table.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
I guess your WWCIW_SAP.ytcurr table in ACP side is not empty before you start copying. So some records with the same unique constraint values that you are trying to copy from the other table obviously allready exists in your destination table.
Jurij :
Before copy, we truncate the table in ACP....so no question of having any records....
try this query
select
KURST,FCURR,TCURR,GDATU,count(1)
from wwciw_sap.ytcurr
group by
KURST,FCURR,TCURR,GDATU
having count(1) > 1;
Mallika,
Indeed by this query we get a dupilcate records....as i heard from my colleague that in PRO PK is with no validate option and can have dulicates.....
Code:
WW15:W_DBA> select kurst, fcurr, tcurr, gdatu, count(1) from ytcurr
2 group by kurst, fcurr, tcurr, gdatu
3 having count(1) > 1
4 /
KURS FCURR TCURR GDATU COUNT(1)
---- ----- ----- -------- ----------
M GRD USD 19990320 2
P USD PTE 19960901 2
Thnaks....but again why will the query with "having option before group by clause" will not work???
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Jurij :
Thnaks....but again why will the query with "having option before group by clause" will not work???
Abhay.
Abhay,
HAVING clause is used with GROUP BY clause and in the syntex it should be after GROUP BY.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
having is like a predicate for aggreate functions
-
Originally posted by SANJAY_G
Abhay,
HAVING clause is used with GROUP BY clause and in the syntex it should be after GROUP BY.
Thas Correct sanjay but i think after 7i it should not be a case...
Our PRO is 8i.
Its irrelevent where ever u have Having clause, result shud be same...
PS below from TEST DB (8i)
Code:
SQL> select * from tabl;
ID
--------------------
1
1
2
2
5
7
6 rows selected.
SQL> select id,count(1) from tabl having count(1)> 1 group by id;
ID COUNT(1)
-------------------- --------------------
1 2
2 2
SQL> select id,count(1) from tabl group by id having count(1)> 1;
ID COUNT(1)
-------------------- --------------------
1 2
2 2
Thanks
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Yes I was wrong, it can be before or after GROUP BY. Just checked, it is same on 7.3.4 as well.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|