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 Go to next message
Mat44
Messages: 4
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 #679164 is a reply to message #679162] Wed, 12 February 2020 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66868
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you create a dblink named GZWR2 and modify the MV definition accordingly?

Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679165 is a reply to message #679164] Wed, 12 February 2020 03:56 Go to previous messageGo to next message
Mat44
Messages: 4
Registered: February 2020
Junior Member
Hi Michel,
I already tried that but the creation failed
ORA-12018: following error encountered during code generation for
"GWEB"."V_ZZ44_BO_AZZRE_LINECREDIT"
ORA-00918: column ambiguously defined
I guess that's because I have some common tables between both db...?


OWNER      DB_LINK                                  USER       SERVICE         CREATED   HID
---------- ---------------------------------------- ---------- --------------- --------- ---
PUBLIC      GZWR2.ZZ44.FR                            GF         gzwr2           12-FEB-20 NO
Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679167 is a reply to message #679165] Wed, 12 February 2020 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66868
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
already tried that but the creation failed

This is another problem and nothing to do with the database link.
Have a look at the query of your view definition, there are several columns with same name in different tables and you didn't qualify the column so Oracle does not know which table you are referring.

Re: ORA-02085 database link AZZRE.CG44.FR connects to GZWR2.ZZ44.FR [message #679171 is a reply to message #679167] Wed, 12 February 2020 06:30 Go to previous messageGo to next message
Mat44
Messages: 4
Registered: February 2020
Junior Member
I 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...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66868
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2914
Registered: January 2010
Location: Connecticut, USA
Senior Member
Mat44 wrote on Wed, 12 February 2020 07:30
I 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 Go to previous messageGo to next message
Mat44
Messages: 4
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 Go to previous message
Solomon Yakobson
Messages: 2914
Registered: January 2010
Location: Connecticut, USA
Senior Member
Mat44 wrote on Wed, 12 February 2020 07:30
I 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.
Previous Topic: ORA-20200: ORA-0000: normal, successful completion when executing PCRD_DF_MANAGEMENT.DF_MANAGE ORA
Next Topic: test failed: ORA-01017 invalid username / password access denied
Goto Forum:
  


Current Time: Wed Feb 26 17:13:49 CST 2020