Oracle用户密码含特殊字符时的sqlplus和数据泵登陆问题
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,\~O(∩_∩)O\~:
① 用户密码含有特殊字符,如@、%、&、¥、#等字符,如何修改密码及登录数据库(重点)
② exp或expdp的时候使用sys用户导出
实验环境介绍
项目 | source db |
---|---|
db 类型 | RAC |
db version | 11.2.0.3.0 |
db 存储 | ASM |
OS版本及kernel版本 | RHEL 6.5 |
实验目标
当用户密码含有特殊字符的时候,测试sqlplus和exp、imp及expdp、impdp的登陆及修改密码问题。
实验过程
sqlplus连接
普通用户连接:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | [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用户连接:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | [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 ~]$ |
expdp连接
密码用双引号,用户名和密码用单引号括起来,然后【用户名】+【密码】+【tn】+【as sysdba】用单引号括起来,最后的这个单引号用\进行转义
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | 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 |
修改密码中含有“@”符号
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | [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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | [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】用单引号括起来,最后的这个单引号用\进行转义
123456789101112 alter user lhr identified by "l@h\r/0";alter user sys identified by "l@h\r/0";set define offalter user scott identified by "$tiger&123l@h\r/0%s,d$";alter user scott identified by "$?<code>$%*H\@f'\<a-q/$-@#<></code>}:H$";password scott
Linux平台 | 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