【方法】Oracle使用者密碼含特殊字元時的登陸問題

1.1 BLOG文件結構圖

1.2 前言部分

1.2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 使用者密碼含有特殊字元，如@、%、&、￥、#等字元，如何修改密碼及登入資料庫（重點）

② exp或expdp的時候使用sys使用者匯出

Tips：

① 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。

② 文章中用到的所有程式碼、相關軟體、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。

③ 若網頁文章程式碼格式有錯亂，請下載pdf格式的文件來閱讀。

④ 在本篇BLOG中，程式碼輸出部分一般放在一行一列的表格中。

本文若有錯誤或不完善的地方請大家多多指正，您的批評指正是我寫作的最大動力。

1.2.2 相關文章連結

【密碼】Oracle使用者密碼系列：http://blog.itpub.net/26736162/viewspace-2129595/

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

第二章實驗部分

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

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

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

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

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

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

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

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

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

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

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工具	資料泵工具（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群，學習最實用的資料庫技術。