Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR (Oracle 12.2, SLES12 SP3)
ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679162] |
Wed, 12 February 2020 03:10 |
|
Mat44
Messages: 6 Registered: February 2020
|
Junior Member |
|
|
Hi All,
I met a problem during the materialized view (MV) creation through a dblink.
The source db is in 12.2, the db which use the dblink is in 11.2.0.3
Every year, 2 MV are created. Usually, that works well, but since the source db have been migrated to 12.2 (11.2.0.4 before) that doesn't work anymore.
The db in 12.2 has a domain name ZZ44.FR whereas the other db which is older doesn't have domain name.
When I tried to create the MV, I have the following message
ORA-02085 database link AZZRE.ZZ44.FR connects to GZWR2.ZZ44.FR
This is not possible to set global_names at FALSE, otherwise, i have the following message when I try to create the MV
ORA-02069: global_names parameter must be set to TRUE for this operation
Here is the dblink
OWNER DB_LINK USER SERVICE CREATED HID
---------- ---------------------------------------- ---------- --------------- --------- ---
PUBLIC AZZRE.ZZ44.FR GF gzwr2 11-FEB-20 NO
I didn't make the MV procedure, and i didn't really know how to adapt it if necessary...
The MV procédure need AZZRE.ZZ44.FR as you can see
....
FROM
SO.PRGF_SER@azzre,
SO.DIRECTIONGF@azzre,
GF.W_STRPOL@azzre,
GF.W_BUDMOI@azzre,
GF.LOC_ENV@azzre,
SO.PRG@azzre,
I'm a bit lost about this problem...
Any clues please?
|
|
|
|
|
|
|
Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679172 is a reply to message #679171] |
Wed, 12 February 2020 07:33 |
|
Michel Cadot
Messages: 68663 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I have neither the query nor the tables definition so can't help.
Just try to execute the query itself in SQL*Plus, it will point you to where the error comes from.
For instance:
SQL> select empno, ename, deptno, dname
2 from emp, dept
3 where emp.deptno = dept.deptno
4 /
select empno, ename, deptno, dname
*
ERROR at line 1:
ORA-00918: column ambiguously defined
[Updated on: Wed, 12 February 2020 07:34] Report message to a moderator
|
|
|
Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679177 is a reply to message #679171] |
Wed, 12 February 2020 14:21 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Mat44 wrote on Wed, 12 February 2020 07:30I was a bit scared about this answer...
The query is pretty long (about 300 lines) and that gonna be difficult for me to change it...
That worked the last year and I didn't update it, so i don't understand why the query could be the problem...
Sure, MV definition didn't change. It is one of MV underlying tables that most likely changed:
SQL> create table table1(id number);
Table created.
SQL> create table table2(table1_id number,name varchar2(20));
Table created.
SQL> create materialized view mv1
2 as
3 select *
4 from table1,
5 table2
6 where id = table1_id
7 /
Materialized view created.
SQL> alter table table1 add name varchar2(10);
Table altered.
SQL> drop materialized view mv1;
Materialized view dropped.
SQL> -- Same MV definition
SQL> create materialized view mv1
2 as
3 select *
4 from table1,
5 table2
6 where id = table1_id
7 /
from table1,
*
ERROR at line 4:
ORA-00957: duplicate column name
SQL>
SY.
|
|
|
Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679182 is a reply to message #679172] |
Thu, 13 February 2020 03:17 |
|
Mat44
Messages: 6 Registered: February 2020
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 12 February 2020 07:33
I have neither the query nor the tables definition so can't help.
Just try to execute the query itself in SQL*Plus, it will point you to where the error comes from.
For instance:
SQL> select empno, ename, deptno, dname
2 from emp, dept
3 where emp.deptno = dept.deptno
4 /
select empno, ename, deptno, dname
*
ERROR at line 1:
ORA-00918: column ambiguously defined
That's right the line 195 should be the problem
ERROR at line 195:
ORA-12018: following error encountered during code generation for
"GWEB"."V_ZZ44_BO_AZZRE_LINECREDIT"
ORA-00918: column ambiguously defined
194 SRUBRIQUE.NUM_SFONC,
195 RUBRIQUE.NUM_FONC,
196 SFONCTION.NUM_FONC,
197 SFONCTION.LIB_FONC,
For the definition, I have that
FROM
SO.PRGF_SER@gzwr2,
SO.FONC_M14@gzwr2 SFONCTION3,
SO.FONC_M14@gzwr2 RUBRIQUE,
SO.FONC_M14@gzwr2 SRUBRIQUE,
SO.FONC_M14@gzwr2 SFONCTION,
SO.FONC_M14@gzwr2 FONCTION,
|
|
|
Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679185 is a reply to message #679171] |
Thu, 13 February 2020 07:24 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Mat44 wrote on Wed, 12 February 2020 07:30I was a bit scared about this answer...
The query is pretty long (about 300 lines) and that gonna be difficult for me to change it...
That worked the last year and I didn't update it, so i don't understand why the query could be the problem...
Sure, MV definition didn't change. It is one of MV underlying tables that most likely changed:
SQL> create table table1(id number);
Table created.
SQL> create table table2(table1_id number,name varchar2(20));
Table created.
SQL> create materialized view mv1
2 as
3 select *
4 from table1,
5 table2
6 where id = table1_id
7 /
Materialized view created.
SQL> alter table table1 add name varchar2(10);
Table altered.
SQL> drop materialized view mv1;
Materialized view dropped.
SQL> -- Same MV definition
SQL> create materialized view mv1
2 as
3 select *
4 from table1,
5 table2
6 where id = table1_id
7 /
from table1,
*
ERROR at line 4:
ORA-00957: duplicate column name
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Tue Jun 18 01:17:36 CDT 2024
|