ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 5763|回复: 3

[笔记] 【方法】Oracle用户密码含特殊字符时的登陆问题

[复制链接]
认证徽章
论坛徽章:
10
青年奥林匹克运动会-高尔夫
日期:2014-09-10 14:54:51火眼金睛
日期:2017-02-06 01:02:33人气徽章
日期:2016-11-09 15:56:29目光如炬
日期:2016-10-30 22:00:00罗罗诺亚·索隆
日期:2016-10-17 12:26:14目光如炬
日期:2016-10-16 22:00:00火眼金睛
日期:2016-11-30 22:00:00目光如炬
日期:2017-01-22 22:00:00美羊羊
日期:2015-04-12 10:40:59火眼金睛
日期:2017-02-28 22:00:00
发表于 2017-3-16 18:33 | 显示全部楼层 |阅读模式
方法Oracle用户密码含特殊字符时的登陆问题
1.1  BLOG文档结构图
1.2  前言部分1.2.1  导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① 用户密码含有特殊字符,如@、%、&、¥、#等字符,如何修改密码及登录数据库(重点)
② exp或expdp的时候使用sys用户导出
Tips:
① 本文在itpub([url=]http://blog.itpub.net/26736162[/url])、博客园([url=]http://www.cnblogs.com/lhrbest[/url])和微信公众号(xiaomaimiaolhr)上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载,小麦苗的云盘地址见:[url=]http://blog.itpub.net/26736162/viewspace-1624453/[/url]
③ 若网页文章代码格式有错乱,请下载pdf格式的文档来阅读。
④ 在本篇BLOG中,代码输出部分一般放在一行一列的表格中。
本文有错误或不完善的地方请大家多多指正,您的批评指正是我写作的最大动力。
1.2.2  相关文章链接
【密码】Oracle用户密码系列:[url=]http://blog.itpub.net/26736162/viewspace-2129595/[/url]
-------------------------------------------------------------------------第二章 实验部分2.1  实验环境介绍
项目
source db
db 类型
RAC
db version
11.2.0.3.0
db 存储
ASM
OS版本及kernel版本
RHEL 6.5
2.2  实验目标
当用户密码含有特殊字符的时候,测试sqlplus和exp、imp及expdp、impdp的登陆及修改密码问题。
2.3  实验过程2.3.1  sqlplus连接
普通用户连接:
[oracle@orcltest admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:25:35 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@lhrdb> alter user lhr identified by "l@hr";
User altered.
SYS@lhrdb> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@orcltest ~]$ sqlplus 'lhr/"l@hr"'@LHRDB
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:27:05 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
LHR@LHRDB> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@orcltest ~]$
sys用户连接:
[oracle@orcltest admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:29:09 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@lhrdb>  alter user sys identified by "l@hr";
User altered.
SYS@lhrdb> exit
[oracle@orcltest ~]$ sqlplus 'sys/"l@hr"'@LHRDB as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 24 09:29:35 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@LHRDB> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@orcltest ~]$
2.3.2  expdp连接
密码用双引号,用户名和密码用单引号括起来,然后【用户名】+【密码】+【tn】+【as sysdba】用单引号括起来,最后的这个单引号用\进行转义
expdp \''sys/"l@hr"'@LHRDB as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y
[oracle@orcltest admin]$  expdp \''sys/"l@hr"'@LHRDB as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y
Export: Release 11.2.0.3.0 - Production on Fri Feb 24 09:32:22 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "sys/********@LHRDB AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=SCOTT%U.dmp SCHEMAS=SCOTT COMPRESSION=all metrics=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 1 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 2 ROLE_GRANT objects in 1 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 1 DEFAULT_ROLE objects in 1 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 10 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 4 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 3 OBJECT_GRANT objects in 3 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 2 INDEX objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 2 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 2 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 1 REF_CONSTRAINT objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 4 TABLE_STATISTICS objects in 14 seconds
. . exported "SCOTT"."DEPT"                              4.976 KB       4 rows
. . exported "SCOTT"."EMP"                               5.617 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          4.890 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u02/app/oracle/admin/lhrdb/dpdump/SCOTT01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:33:19
2.3.3  修改密码中含有“@”符号
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:10:26 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@lhrdb> alter user scott identified by "tiger&123";
Enter value for 123:
old   1: alter user scott identified by "tiger&123"
new   1: alter user scott identified by "tiger"
User altered.
SYS@lhrdb>
SYS@lhrdb> set define off
SYS@lhrdb> alter user scott identified by "tiger&123";
User altered.
SYS@lhrdb> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@orcltest ~]$  sqlplus 'scott/"tiger&123"'@LHRDB
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:14:00 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SCOTT@LHRDB>
SCOTT@LHRDB> set define off
SCOTT@LHRDB> alter user scott identified by "$tiger&123l@h\r/0%s,d$";
User altered.
SCOTT@LHRDB> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@orcltest ~]$  sqlplus 'scott/"$tiger&123l@h\r/0%s,d$"'@LHRDB
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:20:12 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SCOTT@LHRDB>
2.3.4  修改密码中含有双引号符号
修改scott用户的密码为:a"b
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:39:18 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@lhrdb> password scott
Changing password for scott
New password:
Retype new password:
Password changed
SYS@lhrdb> conn scott/a"b
Connected.
SCOTT@lhrdb>
[oracle@orcltest ~]$ sqlplus scott/a\"b   
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 16 17:42:34 2017
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SCOTT@lhrdb>
注:因为一般特殊字符可以使用双引号处理,但是如果密码中含有双引号,就不能用双引号处理,可以直接使用password修改密码。
2.4  本文结论
参考下表:

Linux平台
sqlplus工具
数据泵工具(expexpdp
普通用户
无tns
sqlplus 'lhr/"l@h\r/0"'
expdp 'lhr/"l@h\r/0"'
有tns
sqlplus 'lhr/"l@h\r/0"'@LHRDB
expdp 'lhr/"l@h\r/0"'@LHRDB
sys用户
无tns
sqlplus / as sysdba
expdp \'/ AS SYSDBA\'
有tns
sqlplus 'sys/"l@h\r/0"'@LHRDB as sysdba
expdp \''sys/"l@h\r/0"'@LHRDB as sysdba\'
正常密码
sqlplus sys/lhr@lhrdb as sysdba
expdp \'sys/lhr@LHRDB as sysdba\'
备注:含特殊字符密码为:l@h\r/0,正常密码为:lhr,tns为:LHRDB,总的原则为:密码用双引号括起来,用户名和密码用单引号括起来,然后【用户名】+【密码】+【tns】+【as sysdba】用单引号括起来,最后的这个单引号用\进行转义
alter user lhr identified by "l@h\r/0";
alter user sys identified by "l@h\r/0";
set define off
alter user scott identified by "$tiger&123l@h\r/0%s,d$";
alter user scott identified by "$?`$%*H\@f'\<a-q $-@#`}:H$";
password scott
Windows平台
sqlplus工具
数据泵工具(expexpdp
普通用户
无tns
sqlplus lhr/"""l@h\r/0"""
sqlplus lhr/\"l@h\r/0\"
expdp lhr/"""l@h\r/0"""
expdp lhr/\"l@h\r/0\"
有tns
sqlplus lhr/"""l@h\r/0"""@LHRDB
sqlplus lhr/\"l@h\r/0\"@LHRDB
expdp lhr/"""l@h\r/0"""@LHRDB
expdp lhr/\"l@h\r/0\"@LHRDB
sys用户
无tns
sqlplus / as sysdba
expdp \"/ as sysdba\"
有tns
sqlplus sys/"""l@h\r/0"""@LHRDB as sysdba
sqlplus sys/\"l@h\r/0\"@LHRDB as sysdba
正常密码
sqlplus sys/lhr@lhrdb as sysdba
expdp \"sys/lhr@LHRDB as sysdba\"
备注:含特殊字符密码为:l@h\r/0,正常密码为:lhr,tns为:LHRDB,总的原则为:密码用3个双引号括起来,或者用一个双引号括起来,然后用\将双引号进行转义   DIRECTORY=DATA_PUMP_DIR DUMPFILE=dmp_SCOTT.dmp SCHEMAS=SCOTT  reuse_dumpfiles=y





About Me
...............................................................................................................................
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群:230161599     微信群:私聊
● 联系我请加QQ好友(642808185),注明添加缘由
● 于 2017-03-16 10:00 ~ 2017-03-16 22:00 在泰兴公寓完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。
  


非常感谢各位朋友支持小麦苗。
论坛徽章:
0
发表于 2017-3-17 12:31 | 显示全部楼层
lz总结的好好哇

使用道具 举报

回复
认证徽章
论坛徽章:
1
秀才
日期:2016-12-21 16:55:07
发表于 2017-3-20 17:35 | 显示全部楼层

使用道具 举报

回复
求职 : 数据分析/ETL
论坛徽章:
6
鲜花蛋
日期:2014-08-09 15:15:33itpub13周年纪念徽章
日期:2014-10-05 10:01:16优秀写手
日期:2014-03-12 06:00:12暖羊羊
日期:2015-03-04 14:54:572015年新春福章
日期:2015-03-06 11:59:47秀才
日期:2017-04-05 13:22:59
发表于 2017-3-21 18:02 | 显示全部楼层
双引号

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

SACC2017购票7.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

活动链接>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表