近期有一个项目要上线，开发人员在测试生产数据库的用户是否满足需求时，遇到了ORA-02019错误。

SQL> select * 
  2  from (select m.brd_pgm_schd_id || d.brd_pgm_schd_seq as adv_id, 
  3  m.brd_bgn_dtm as ymd, 
  4  m.brd_cl_dtm as eymd, 
  5  (case 
  6  when m.use_yn = 'Y' then 
  7  1 
  8  else 
  9  2 
 10  end) as onoff, 
 11  m.inst_dtm as itime, 
 12  m.inst_id as ioptr, 
 13  m.mdf_dtm as utime, 
 14  d.prd_id as good_id, 
 15  (select legacy_class_id 
 16  from chhmif.PRD_CHNL_M 
 17  where chnl_id = m.chnl_id) as class_id 
 18  from chhmif.med_brd_pgm_schd_m m 
 19  left join chhmif.med_brd_pgm_prd_schd_d d on m.brd_pgm_schd_id = 
 20  d.brd_pgm_schd_id 
 21  where m.brd_bgn_dtm > 
 22  to_date('2015-02-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
 23  and (select legacy_class_id 
 24  from chhmif.PRD_CHNL_M 
 25  where chnl_id = m.chnl_id) = 1 
 26  and m.chnl_id = '3001' 
 27  order by m.brd_std_date desc) t 
 28  union 
 29  select * 
 30  from (select m.brd_pgm_schd_id || d.brd_pgm_schd_seq as adv_id, 
 31  m.brd_bgn_dtm as ymd, 
 32  m.brd_cl_dtm as eymd, 
 33  (case 
 34  when m.use_yn = 'Y' then 
 35  1 
 36  else 
 37  2 
 38  end) as onoff, 
 39  m.inst_dtm as itime, 
 40  m.inst_id as ioptr, 
 41  m.mdf_dtm as utime, 
 42  d.prd_id as good_id, 
 43  (select legacy_class_id 
 44  from chhmif.PRD_CHNL_M 
 45  where chnl_id = m.chnl_id) as class_id 
 46  from chhmif.med_brd_pgm_schd_m m 
 47  left join chhmif.med_brd_pgm_prd_schd_d d on m.brd_pgm_schd_id = 
 48  d.brd_pgm_schd_id 
 49  where m.mdf_dtm > 
 50  to_date('2015-02-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
 51  and (select legacy_class_id 
 52  from chhmif.PRD_CHNL_M 
 53  where chnl_id = m.chnl_id) = 1 
 54  and m.chnl_id = '3001' 
 55  order by m.brd_std_date desc) t1
 56  ;
left join chhmif.med_brd_pgm_prd_schd_d d on m.brd_pgm_schd_id =
                 *
ERROR at line 19:
ORA-02019: connection description for remote database not found

ORA-02019错误通常是在本地数据库通过DB LINK连接远程数据库时才会遇到，但是通过上面的SQL可以看到SQL并没有使用DB LINK，因此猜测chhmif.med_brd_pgm_prd_schd_d可能不是表，很有可能是视图或者同义词。按照我的思路和习惯，先确定下故障是否出现在chhmif.med_brd_pgm_prd_schd_d对象上。

SQL> desc chhmif.med_brd_pgm_prd_schd_d 
ERROR:
ORA-02019: connection description for remote database not found

确定故障的确是出现在chhmif.med_brd_pgm_prd_schd_d对象上，那么查看下这个到底是什么类型的对象。

SQL> select object_name,object_type,owner from dba_objects where object_name=upper('med_brd_pgm_prd_schd_d');

OBJECT_NAME                 OBJECT_TYPE         OWNER
----------------------      ------------        -------------
MED_BRD_PGM_PRD_SCHD_D      SYNONYM             CHHMIF
MED_BRD_PGM_PRD_SCHD_D      SYNONYM             CHHMIF_READ

可见chhmif.med_brd_pgm_prd_schd_d还真是同义词，那么就好办了，查看下这个同义词的创建语句，看看是否使用了DB LINK。

SQL>  select dbms_metadata.get_ddl('SYNONYM','MED_BRD_PGM_PRD_SCHD_D','CHHMIF') FROM DUAL;

DBMS_METADATA.GET_DDL('SYNONYM','MED_BRD_PGM_PRD_SCHD_D','CHHMIF')
--------------------------------------------------------------------------------
  CREATE OR REPLACE SYNONYM "CHHMIF"."MED_BRD_PGM_PRD_SCHD_
D" FOR "CHGSHS"."MED_BRD_PGM_PRD_SCHD_D"
@"LINK_IVL2IF"

到这里基本可以确定，开发人员使用的IVL_READ用户很可能没有使用LINK_IVL2IF这个DB LINK的权限，下面查看下DB LINK的信息。

SQL> select OWNER,DB_LINK,USERNAME from dba_db_links where DB_LINK='LINK_IVL2IF';

OWNER                          DB_LINK                   USERNAME
------------------------------ ------------------------- ------------------------------
CHHMIF                         LINK_IVL2IF               IVL2IF
CHHMIF_READ                    LINK_IVL2IF               IVL2IF

可见LINK_IVL2IF这个DB LINK都是私有的，并不是公有的，那么IVL_READ用户肯定是没有权限使用这个DB LINK的，这样解决方法就很简单了，只要在IVL_READ用户下根据这个DB LINK的信息创建这个DB LINK就可以了。

SQL> create database link LINK_IVL2IF
  2    connect to IVL2IF IDENTIFIED BY "xxxxxxxxx"
  3    using '(DESCRIPTION =
  4      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.100)(PORT = 1521))
  5      (LOAD_BALANCE = no)
  6      (CONNECT_DATA =
  7        (SERVER = DEDICATED)
  8        (SERVICE_NAME = ivldb)
  9      )
 10    )';

Database link created.

创建DB LINK后，这个SQL就可以成功运行了。

SQL> select count(*) from 
  2  （select *
  3  from (select m.brd_pgm_schd_id || d.brd_pgm_schd_seq as adv_id, 
  4  m.brd_bgn_dtm as ymd, 
  5  m.brd_cl_dtm as eymd, 
  6  (case 
  7  when m.use_yn = 'Y' then 
  8  1 
  9  else 
 10  2 
 11  end) as onoff, 
 12  m.inst_dtm as itime, 
 13  m.inst_id as ioptr, 
 14  m.mdf_dtm as utime, 
 15  d.prd_id as good_id, 
 16  (select legacy_class_id 
 17  from chhmif.PRD_CHNL_M 
 18  where chnl_id = m.chnl_id) as class_id 
 19  from chhmif.med_brd_pgm_schd_m m 
 20  left join chhmif.med_brd_pgm_prd_schd_d d on m.brd_pgm_schd_id = 
 21  d.brd_pgm_schd_id 
 22  where m.brd_bgn_dtm > 
 23  to_date('2015-02-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
 24  and (select legacy_class_id 
 25  from chhmif.PRD_CHNL_M 
 26  where chnl_id = m.chnl_id) = 1 
 27  and m.chnl_id = '3001' 
 28  order by m.brd_std_date desc) t 
 29  union 
 30  select * 
 31  from (select m.brd_pgm_schd_id || d.brd_pgm_schd_seq as adv_id, 
 32  m.brd_bgn_dtm as ymd, 
 33  m.brd_cl_dtm as eymd, 
 34  (case 
 35  when m.use_yn = 'Y' then 
 36  1 
 37  else 
 38  2 
 39  end) as onoff, 
 40  m.inst_dtm as itime, 
 41  m.inst_id as ioptr, 
 42  m.mdf_dtm as utime, 
 43  d.prd_id as good_id, 
 44  (select legacy_class_id 
 45  from chhmif.PRD_CHNL_M 
 46  where chnl_id = m.chnl_id) as class_id 
 47  from chhmif.med_brd_pgm_schd_m m 
 48  left join chhmif.med_brd_pgm_prd_schd_d d on m.brd_pgm_schd_id = 
 49  d.brd_pgm_schd_id 
 50  where m.mdf_dtm > 
 51  to_date('2015-02-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') 
 52  and (select legacy_class_id 
 53  from chhmif.PRD_CHNL_M 
 54  where chnl_id = m.chnl_id) = 1 
 55  and m.chnl_id = '3001' 
 56  order by m.brd_std_date desc) t1
 57  )
 58  ;

  COUNT(*)
----------
     52018

由于这个SQL查询结果太多，这里简单的改成COUNT运算，至此，问题解决。

本文固定链接: https://www.dbdream.com.cn/2016/07/ora-02019-connection-description-for-remote-database-not-found%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！