合 Oracle用户密码含特殊字符时的sqlplus和数据泵登陆问题
👉 本文共约2072个字，系统预计阅读时间或需8分钟。
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~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】用单引号括起来，最后的这个单引号用\进行转义
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
