轉載於---&gt>http://blog.csdn.net/leshami/article/details/6688380
ORA-02019 錯誤提示是未找到遠端資料庫的連線說明，通常發生在本地資料庫無法連線到遠端資料庫。引發該問題的原因很多，比如網路連線，連線方式(tnsnames)，dblinkc的建立等等。而下面的描述的故障則比較奇特。

一、錯誤現象

[sql] view plain copy print ?

SQL> select * from scanfilename@dss.m85;



select * from scanfilename@dss.m85



ORA-02019: 未找到遠端資料庫的連線說明



SQL> select * from xdoc_file@DSS.M85;



select * from xdoc_file@DSS.M85



ORA-02019: 未找到遠端資料庫的連線說明

二、分析與解決
1.當前資料庫版本

[sql] view plain copy print ?

SQL> select * from v$version;



BANNER

----------------------------------------------------------------

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

2.當前已建立的database link

[sql] view plain copy print ?

SQL> select * from dba_db_links;



OWNER                          DB_LINK    USERNAME   HOST            CREATED

------------------------------ ---------- ---------- --------------- -----------

DIMS                           DSS.M85    VIEWPIC    PIC             2007-11-8 1



SQL> select * from v$dblink;   --&gt當前沒有正在使用的database link



DB_LINK      OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH

---------- ---------- --------- ------------- -------- ------------ -------------- ----------- ---------------------

3.下面的儲存過程使用了分散式資料庫

[sql] view plain copy print ?

SQL> select text from dba_source where name='P_PROCESS_PIC_VALID';



TEXT

--------------------------------------------------------------------------------

PROCEDURE "P_PROCESS_PIC_VALID"

       (processDate IN DATE --處理日期，如果不傳入，就表示當天

       ) AS

       recCount INT;

       pictureName NVARCHAR2(50);

       mntnOperMask VARCHAR2(10);     --維護部操作人員賬號字首

       oper        NVARCHAR2(50);

       PicId  NVARCHAR2(50);

       FilmId NVARCHAR2(50);

       v_maxid      number;       --20100720

BEGIN

    -------------------------------------------------20100720

    -- 已匯入的資料最大的ID

    select max(id) into v_maxid from T_scanfilename_m85;

    -- 匯入新增的資料

    execute immediate 'insert into T_scanfilename_m85

                      select * from scanfilename@dss.m85



TEXT

--------------------------------------------------------------------------------

                      where id>:COL_1'

    using v_maxid;

    commit;

    -------------------------------------------------20100720

    mntnOperMask := 'gl';

     --從圖系統匯入有效片資料

     INSERT INTO t_pic_xdoc_file(cph,cl,wldd,fx,wlrq,wlxm,lrsj,oper,picture_name

       SELECT T1.cph,T1.cl,T1.wldd,T1.fx,T1.wlrq,T1.wlxm,T1.lrsj,T1.oper,T2.FILE

         /*FROM xdoc_file@DSS.M85 T1,scanfilename@DSS.M85 T2*/ --20100720

         FROM xdoc_file@DSS.M85 T1,T_scanfilename_m85 T2

         WHERE T1.XH=T2.XH

             and t2.id>15717858

             and not exists (select picture_name from t_pic_xdoc_file where pict

             AND substr(T1.oper,1,2)= mntnOperMask

             and (t1.lrsj >=sysdate-3);

     -- 將圖文系統過來的有效片資料，分析處理後儲存到統計表中

     --處理有效片

     FOR c IN(SELECT xf.*,ROWID FROM t_pic_xdoc_file xf WHERE status=1)

     LOOP



SQL> select table_name,owner from dba_tables where table_name in ('SCANFILENAME','XDOC_FILE');



TABLE_NAME                     OWNER

------------------------------ ------------------------------

SCANFILENAME                   DIMS



SQL> select * from dims.scanfilename where rownum<5;



                                     ID FILENAME                                           XH

--------------------------------------- -------------------------------------------------- ------------

                                8606178 V1_00000020361002320070614095523017900004.jpg

                                8605441 V1_00000002101002120070614095523011900023.jpg

                                8605445 V1_00000002101002120070614095523011900025.jpg

                                8605449 V1_00000002101002120070614095523011900027.jpg

4.嘗試執行操作

[sql] view plain copy print ?

SQL> select * from scanfilename@dss.m85;



select * from scanfilename@dss.m85



ORA-02019: 未找到遠端資料庫的連線說明



SQL> select * from xdoc_file@DSS.M85;



select * from xdoc_file@DSS.M85



ORA-02019: 未找到遠端資料庫的連線說明



SQL> show user;

User is "SYS"



SQL> conn dims/dims_12345@dmsdb

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.1

Connected as dims



SQL> select * from dba_db_links;



OWNER           DB_LINK         USERNAME        HOST            CREATED

--------------- --------------- --------------- --------------- -----------

DIMS            DSS.M85         VIEWPIC         PIC             2007-11-8 1



SQL> select * from user_db_links;



DB_LINK         USERNAME        PASSWORD                       HOST            CREATED

--------------- --------------- ------------------------------ --------------- -----------

DSS.M85         VIEWPIC         VIEWPIC                        PIC             2007-11-8 1

5.切換帳戶後使用分散式資料庫，提示監聽不可用

[sql] view plain copy print ?

SQL> select * from xdoc_file@DSS.M85



ORA-12514: TNS: 監聽程式不能解析在連線描述符中給出的 SERVICE_NAME



SQL> select * from scanfilename@DSS.M85;



select * from scanfilename@DSS.M85



ORA-12514: TNS: 監聽程式不能解析在連線描述符中給出的 SERVICE_NAME



pic =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.31)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DSS)

    )

  )

6.使用tnsping測試

[sql] view plain copy print ?

C:\>tnsping pic



TNS Ping Utility for 32-bit Windows: Version 8.1.7.4.0 - Production on 26-7月 -2

011 15:32:35

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=100.100.1.31)(PORT=1521))

OK（20毫秒）



C:\>sqlplus viewpic/viewpic@pic    --&gtsqlplus 不能登陸



SQL*Plus: Release 8.1.7.0.0 - Production on 星期二 7月 26 15:33:15 2011



(c) Copyright 2000 Oracle Corporation.  All rights reserved.



ERROR：

ORA-12514: TNS: 監聽程式不能解析在連線描述符中給出的 SERVICE_NAME

7.檢視本地tnsnames.ora

[sql] view plain copy print ?

pic =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.1.31)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = DSS)  --&gt(原來為service_name,將其改為SID)

    )

  )



C:\>sqlplus viewpic/viewpic@pic    --&gt再次使用sqlplus登陸正常



SQL*Plus: Release 8.1.7.0.0 - Production on 星期三 7月 27 09:49:57 2011

(c) Copyright 2000 Oracle Corporation.  All rights reserved.



連線到:

Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production

With the Partitioning and Parallel Server options

JServer Release 8.1.7.0.0 - 64bit Production



SQL> select * from v$version;



BANNER

----------------------------------------------------------------

Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production

PL/SQL Release 8.1.7.0.0 - Production

CORE    8.1.7.0.0       Production

TNS for IBM/AIX RISC System/6000: Version 8.1.7.0.0 - Developmen

NLSRTL Version 3.4.1.0.0 - Production



SQL> select * from scanfilename@DSS.M85 where rownum



        ID FILENAME                                           XH

---------- -------------------------------------------------- --------------------

   8606178 V1_00000020361002320070614095523017900004.jpg

   8605441 V1_00000002101002120070614095523011900023.jpg

   8605445 V1_00000002101002120070614095523011900025.jpg

8.是什麼原因造成了需要將service_name改為SID，以前使用service_name一直正常。由於DSS.M85在故障前前曾重
未做任何更改。Oracle 8之後推薦使用service_name，但實際上在Oracle 8使用service_name也可以正常使用。

ORA-02019 錯誤處理

相關文章