Covers all SQL & PL/SQL Tutorial along with Interview questions and Objective Questions . SQL Encyclopedia.
Sunday, November 4, 2012
How to get the last password changed time for a oracle user
Question: How to get the last password changed time for a oracle user? Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user.
Try this query:
SELECT name, ctime, ptime FROM sys.user$ WHERE name = 'USER-NAME';
Note: Replace USER-NAME with the user name which you want to know the information.
CTIME Indicates - Creation Time PTIME Indicates - Password Change Time
Here's the table DESCription: Name Type Nullable Default Comments ------------ ------------------- -------- ------- -------- USER# NUMBER NAME VARCHAR2(30 BYTE) TYPE# NUMBER PASSWORD VARCHAR2(30 BYTE) Y DATATS# NUMBER TEMPTS# NUMBER CTIME DATE PTIME DATE Y EXPTIME DATE Y LTIME DATE Y RESOURCE$ NUMBER AUDIT$ VARCHAR2(38 BYTE) Y DEFROLE NUMBER DEFGRP# NUMBER Y DEFGRP_SEQ# NUMBER Y ASTATUS NUMBER 0 LCOUNT NUMBER 0 DEFSCHCLASS VARCHAR2(30 BYTE) Y EXT_USERNAME VARCHAR2(4000 BYTE) Y SPARE1 NUMBER Y SPARE2 NUMBER Y SPARE3 NUMBER Y SPARE4 VARCHAR2(1000 BYTE) Y SPARE5 VARCHAR2(1000 BYTE) Y SPARE6 DATE Y