Oracle认证：Oracle用户密码含有特殊字符的处理办法

会计考友

在设置Oracle密码时，我们经常会将密码设置的特别复杂（一般由字母、数字和特殊符号组成），值得注意的是，在有特殊符号时，修改密码或着导入导出数据时会遇到很多不必要的麻烦，本文中将会对此情况进行详细的解释和说明：
注:本文中，将以特殊符号“/”为例。
: F" \# E$ v# a( gLinux环境下，使用Oracle数据库10g
修改密码
[oracle@olivenan oracle]$ sqlplus '/as sysdba'
7 Z6 A/ [# `2 l; R: C
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 17 14:33:32 2006
, H6 E) z1 K, V6 @6 E  \& p2 o$ N
Copyright (c) 1982, 2005, Oracle. All rights reserved.

9 P% U1 ~) r. x2 l& s! q+ g
Connected to:
$ H! `8 [! C7 `9 z9 r5 O/ y9 n# uOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

/ s7 b& j5 }4 Z4 q- B( LSQL> alter user test identified by aa/aa;
6 G& M( O9 J7 c' S/ x; |: {9 j
alter user test identified by aa/aa
6 p5 y: b8 w" B, t*
9 g$ q; L) c! fERROR at line 1:
2 t5 T; ?( e: B, a4 ?) `ORA-00922: missing or invalid option

在此使用""
SQL> alter user test identified by "aa/aa";
8 s' @9 \$ x; U$ U# w7 `& d
User altered.
  Q/ a( Y1 i! K& R
SQL>
* ~  W. @' S6 s/ H  [- w
* Y) q, v+ `0 h  Y+ _2 i) V
8 _9 u* I1 ^, M/ r[oracle@olivenan oracle]$ exp test/aa/aa file=test.dmp buffer=65536

3 x: q# C) B' l  k* k  \Export: Release 10.2.0.1.0 - Production on Wed May 17 14:32:10 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.
8 N  B( b0 {% v4 O" ]& }

& F( |, |# R# m/ Y4 J; i% A, gEXP-00004: invalid username or password
Username:

此处应该进行转义使用 " "处理密码，使用 ' '处理用户名和密码
3 @" Y$ r, q3 J2 u2 ]
( p3 }$ S, n3 S( Q  g[oracle@olivenan oracle]$ exp 'test/"aa/aa"' file=test.dmp buffer=65536
" A. K9 I, f7 {+ c
Export: Release 10.2.0.1.0 - Production on Wed May 17 14:32:52 2006
/ d2 I6 M5 G! J' O6 z; r
Copyright (c) 1982, 2005, Oracle. All rights reserved.
3 z# [) J5 x. c5 s% q
) a4 L. {% A  N: M
$ u, g2 v+ ~7 yConnected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
! ~3 O+ n: E  m) l6 x: hWith the Partitioning, OLAP and Data Mining options
, h+ a  w1 V0 J$ u% |$ w# j0 j. HExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified users ...
/ O5 l& X% j* h' F8 N. exporting pre-schema procedural objects and actions
) J1 c: Y# W: W( p* t3 o: y
4 |0 l+ p& m3 W2 u...............
/ t, L/ q* ~: u8 W导出成功。 　Windows Xp下，数据库为Oracle9i

1 E/ ~: Q( N, S* H) M, N
% h) l+ Q8 l" J% ?
C:\Documents and Settings\w>sqlplus /nolog
1 i2 M# M' N6 s1 [( _
SQL*Plus: Release 9.2.0.1.0 - Production on Wed May 17 14:56:34 2006
0 b- |* M0 p. q; c
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
3 {0 M7 Z0 X; d' n
SQL> connect sys/olivenan as sysdba
Connected.
SQL> alter user test identified by aa/aa;
- r4 m/ H& G! M1 z2 `: [+ R5 n; D. j; ealter user test identified by aa/aa
*

会计考友

ERROR at line 1:
2 B( u7 L2 z/ T5 v- f1 d+ r6 F8 d% sORA-00922: missing or invalid option
& Z  v+ q$ }+ C& o; _, {
此处使用""
5 v" w6 E; P% P6 W& X2 t. Z7 DSQL> alter user test identified by "aa/aa";

9 w3 J' T4 Q  ^0 ~3 t2 z) q; r' L6 A0 VUser altered.

SQL>

6 T- g+ f0 Z8 yMicrosoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\w>exp test/aa/aa file=aa.dmp buffer=65536
4 u9 ?0 p9 }- ~' K: M
( x* d/ R1 H. t9 L1 O! G( ?6 r, rExport: Release 9.2.0.1.0 - Production on Wed May 17 14:58:50 2006
4 R1 _3 Y* O2 F* Q
1 {- Z, @' S+ kCopyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
7 x! m) K. x9 ]

EXP-00004: invalid username or password
7 x! i1 U+ o9 @2 EUsername:
Password:
EXP-00056: ORACLE error 1017 encountered
( P: a0 i6 D8 _9 \- MORA-01017: invalid username/password; logon denied
) H1 ?& ?% t8 |4 ?Username: ^Z^Z

EXP-00030: Unexpected End-Of-File encountered while reading input
EXP-00000: Export terminated unsuccessfully

在此处将使用""" """来处理用户密码

' x4 \  ^$ H" @  sC:\Documents and Settings\w>exp test/"""aa/aa""" file=aa.dmp buffer=65536
: g! \+ t. L9 d0 O! Y5 q% a4 Y  B) F
Export: Release 9.2.0.1.0 - Production on Wed May 17 14:59:10 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

' J1 E7 \& r. T
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
- P" J) O" A* a9 H5 G, y9 aJServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
5 G! Z4 H5 [+ `" q9 D  r$ Kserver uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
, o; s; p1 l0 I& [( a# x# ~. exporting foreign function library names for user test
. exporting PUBLIC type synonyms
. exporting private type synonyms
; y; T& F8 ]" e8 a8 s; e: m* [. exporting object type definitions for user test
2 F. o" M1 N& T$ ]! p9 Y4 j4 WAbout to export test's objects ...
. exporting database links
. exporting sequence numbers ........
" i7 n0 [3 ^3 j" A至此，导出成功。