連結： http://blog.itpub.net/28602568/viewspace-1278993/

標題： Oracle 9i DBLINK連線11G 錯誤ora-01017,ora-02063的解決方案

今天客戶要在9i 環境建立dblink 讀取11g 資料，在建立後檢驗dblink是否有效時；

報錯【 ORA-01017: invalid username/password; logon denied；ORA-02063: 緊接著line（源於DB_XX）】

分析原因：Oracle 11g 開始使用者的密碼才區分大小寫；

測試：11g前密碼是否會自動轉換大寫？

11g 區分大小寫

[oracle@xcd ~]$ sqlplus BACKUP2014/BACKUP2014

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 23 17:30:50 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

9i 資料庫建立dblink

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as haikuo@192.168.0.31/orapp

SQL> create database link db_test connect to backup2014 identified by backup2014 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.1 )(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = sinodb)))';

Database link created

校驗dblink：

SQL> SELECT usename FROM user_users@db_test;

USERNAME
------------------

BACKUP2014

解決方案將小寫密碼加雙引號如下例子..
SQL> create database link db_sinotest_link connect to "sinotest_link" identified by "maohaiqing" using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.2)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = testdb)))';

Database link created

校驗dblink：

SQL> SELECT usename FROM user_users@db_sinotest_link;

USERNAME
------------------
SINOTEST_LINK

SQL>

提示：

11g可以透過引數sec_case_sensitive_logon或dba_users.password_versions欄位查詢密碼大小寫是否敏感；

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle 9i DBLINK連線11G 錯誤ora-01017,ora-02063的解決方案

相關文章