溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
【方法】Oracle用戶密碼含特殊字符時的登陸問題
【密碼】Oracle用戶密碼系列：http://blog.itpub.net/26736162/viewspace-2129595/
項目 | source db |
db 類型 | RAC |
db version | 11.2.0.3.0 |
db 存儲 | ASM |
OS版本及kernel版本 | RHEL 6.5 |
當用戶密碼含有特殊字符的時候，測試sqlplus和exp、imp及expdp、impdp的登陸及修改密碼問題。
普通用戶連接：
[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 ~]$
|
密碼用雙引號，用戶名和密碼用單引號括起來，然后【用戶名】+【密碼】+【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 DUMPFIL 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
|
[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>
|
修改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修改密碼。
參考下表：
Linux平臺 | sqlplus工具 | 數據泵工具（exp、expdp） | |
普通用戶 | 無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工具 | 數據泵工具（exp、expdp） | |
普通用戶 | 無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）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2135493/
● 本文博客園地址：http://www.cnblogs.com/lhrbest/p/6560906.html
● 本文pdf版及小麥苗云盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 聯系我請加QQ好友(642808185)，注明添加緣由
● 于 2017-03-16 10:00 ~ 2017-03-16 22:00 在泰興公寓完成
● 文章內容來源于小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的數據庫技術。
cdn.qqmail.com/zh_CN/htmledition/p_w_picpaths/function/qm_open/ico_mailme_02.png">
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
