Oracle User Password Reset : Two different methods – SQL*Plus and alter user

Method 1: Using SQL*Plus password command

If you connected as sys and trying to reset the password for other user it will not ask for old password.

SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> password scott
Changing password for scott
New password:
Retype new password:
Password changed

Reset the user password from its own login.

SQL> connect scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:
Password changed

Method 2: using alter user command

SQL>  alter user scott identified by tiger;

User altered.

How to deal with alpha numeric character in the password?

If you include any alpha numeric character in the password the alter user command will not accept without double quotes. See the example below. For the SQL*Plus password command it doesn’t matter as it takes the alpha numeric charters without quotes.

SQL> alter user scott identified by 123*new;
alter user scott identified by 123*new
                                   *
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> alter user scott identified by "123*new";

User altered.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google