oracle密码问题

*************
  密码问题
*************

--设定密码

[oracle@ora11 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 2 07:16:28 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user bob identified by abc123!;
create user bob identified by abc123!
                                    *
ERROR at line 1:
ORA-00922: missing or invalid option

--密码中出现特殊字符时应该加入引号
SQL> create user bob identified by "abc123!";

User created.

--如果密码里存在&字符，责客户端会认为是变量
SQL> create user maclean_password1 identified by “0000&a”;
Enter value for a:

--处理方法如下：
SQL> set define +
SQL> show define
define "+" (hex 2b)

SQL> create user tom identified by "0000&a";

User created.

SQL>

--忘记密码

场景：数据库需要移植，库中某一个用户的Oracle用户密码不知道，可是这个密码因为在其他程序中使用了而不能修改，在新的数据库系统中，这个Oracle用户密码还要和原密码相同。

SQL> select username,password from dba_users where username='';
SQL> alter user jw_pub1 identified by values 'xxx';

--注意在oracle11g中用户的密文不存在dba_user表中，可以再user$中找到它
--例如
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> desc user$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER#                                     NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 DATATS#                                   NOT NULL NUMBER
 TEMPTS#                                   NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 PTIME                                              DATE
 EXPTIME                                            DATE
 LTIME                                              DATE
 RESOURCE$                                 NOT NULL NUMBER
 AUDIT$                                             VARCHAR2(38)
 DEFROLE                                   NOT NULL NUMBER
 DEFGRP#                                            NUMBER
 DEFGRP_SEQ#                                        NUMBER
 ASTATUS                                   NOT NULL NUMBER
 LCOUNT                                    NOT NULL NUMBER
 DEFSCHCLASS                                        VARCHAR2(30)
 EXT_USERNAME                                       VARCHAR2(4000)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE

SQL> select NAME,PASSWORD from user$ where name='SCOTT';

NAME                           PASSWORD
------------------------------ ------------------------------
SCOTT                          98AB4026C1A013FE

SQL> alter user scott identified by values '98AB4026C1A013FE';

User altered.

SQL> conn scott/123;
Connected.
SQL>

--注意：要想利用密文来重置oracle用户密码，对应用的profile必须是default！！！ 10g/11g都测试过了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28278387/viewspace-746772/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28278387/viewspace-746772/