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>
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
------------------
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)))';
校验dblink：
SQL> SELECT usename FROM user_users@db_sinotest_link;
解决方案 将小写密码加双引号如下例子..
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字段查询 密码大小写是否敏感；
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28602568/viewspace-1278993/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle 跟踪/告警/监听日志的清理脚本
- Oracle JAVA变量类型与字段类型不一致，分区表全扫(INTERNAL_FUNCTION隐含转换)的优化
- EXPDP dumpfile和parallel的关系
- Oracle not exist子查询全扫的优化
- Oracle 不知用户的密码时,如何获取dblink创建语句
- Oracle 快速将数据导出到CSV(Excel)文件的方法介绍
- Redis 基本搭建
- PLSQL Developer Profiler 使用 【存储过程调优 工具推荐】
- Oracle 针对子查询里有group by 表全扫的优化
- Oracle 使用sqlnet.ora/trigger限制/允许某IP或IP段访问指定用户
