Getting Unique Constraints while copying from 1 DB to other.

Printable View

04-08-2003, 03:56 AM
abhaysk

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.
04-08-2003, 04:18 AM
akhadar

If u are using any sequences better check this from metalink

ID:Note:125720.1
04-08-2003, 04:28 AM
abhaysk

No sequences are not invloved here.

Thanks anyway

Abhay.
04-08-2003, 05:40 AM
mallika

try this query
select
KURST,FCURR,TCURR,GDATU,count(1)
from wwciw_sap.ytcurr
group by
KURST,FCURR,TCURR,GDATU
having count(1) > 1;
04-08-2003, 06:39 AM
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.
04-08-2003, 11:02 PM
abhaysk

Quote:

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....

Quote:

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.
04-09-2003, 01:45 AM
SANJAY_G

Quote:

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.
04-09-2003, 02:45 AM
pando

having is like a predicate for aggreate functions
04-09-2003, 02:48 AM
abhaysk

Quote:

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.
04-09-2003, 03:08 AM
SANJAY_G

Yes I was wrong, it can be before or after GROUP BY. Just checked, it is same on 7.3.4 as well.
04-09-2003, 03:32 AM
abhaysk

Quote:

Originally posted by SANJAY_G
Yes I was wrong, it can be before or after GROUP BY. Just checked, it is same on 7.3.4 as well.

Sanjay :

7i Sometimes gives erratic results ( this is wat i have heard from my colleagues...infact i never used 7i)

Now coming to the original problem....

Quote:

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

Quote:

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

Why Oracle is behaving stupidly here.....

Thanks
Abhay.
04-09-2003, 11:16 PM
abhaysk

Pando, Jurij and all...

Any explaination as to why oracle is giving different results for logically same querys?
:confused: :rolleyes:

Version :

Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Abhay.