In this article, we are going to learn how to check past password change history in the oracle database. Using the below commands you can check the password change history step by step.
Read: What is control file and datafile in Oracle?
When was my Oracle password last changed?
The below query will show you the timestamp of the last password change. Here I'm checking SCOTT user password change time.
SQL> select user#,name,PASSWORD,to_char(CTIME,'dd-mon-yyyy hh24:mi:ss')Create_time,to_char(PTIME,'dd-mon-yyyy hh24:mi:ss') Change_time from user$ where name='SCOTT';Output from the above query:
CTIME - Time of user creation. PTIME - Password Change Time.
What is Oracle password history?
Every Oracle database user has a profile and in the user profile, we set the limit of below two parameters.
- PASSWORD_REUSE_MAX
- PASSWORD_REUSE_TIME
We can set any value for these parameters or set it UNLIMITED. The Oracle store the password change history in user_history$ table which is available in SYS user.
How To Track Password Changed Dates Of An Oracle User?
Using below query you can track the password changed dates with the help of user_history$ and user$.
SQL> Select name,password_date "Password Changed date"
from sys.user$ A ,sys.user_history$ B
where A.user# = B.user# and A.name='SCOTT'
order by password_date;How to retrieve user password in oracle 11g?
Using the below query you can reset or retrieve the current password in Oracle.
SQL> set long 9999
SQL> set lin 300 pagesize 300
SQL> with t as
( select dbms_metadata.get_ddl('USER','SCOTT') ddl from dual )
select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';' QUERY
from t;Output from the above Query:
Subscribe on YouTube