{"id":1113,"date":"2025-01-03T07:31:03","date_gmt":"2025-01-03T07:31:03","guid":{"rendered":"https:\/\/w3buddy.com\/?p=1113"},"modified":"2026-01-15T13:17:29","modified_gmt":"2026-01-15T07:47:29","slug":"oracle-sql-scripts-to-check-user-password-change-account-creation-last-login-expiry-date-and-password-history","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-sql-scripts-to-check-user-password-change-account-creation-last-login-expiry-date-and-password-history\/","title":{"rendered":"Oracle SQL Scripts for User Password, Account Info, and History"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In Oracle, it is essential to monitor and manage user account details such as password changes, account creation, last login times, password expiry dates, and password change history. This post will walk you through the most useful SQL scripts to retrieve this information.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Check User Account Creation Date<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To check the account creation date for a specific user, use the following query:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';\nSELECT CREATED FROM DBA_USERS WHERE USERNAME = 'user_name';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2. Check Last Password Change Time<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To check the last time the password was changed for a specific user, use this query:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';\nSELECT PTIME FROM SYS.USER$ WHERE NAME = 'user_name';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">After altering the password:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SQL> ALTER USER HR IDENTIFIED BY TestPwd2025#;\nUser altered.\n\nSQL> SELECT PTIME FROM SYS.USER$ WHERE NAME = 'HR';\nPTIME\n---------\n07-JAN-2024<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. Check Last Login Date<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To check when a user last logged in, use this query:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT LAST_LOGIN FROM DBA_USERS WHERE USERNAME = 'user_name';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">4. Check Password Expiry Date<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To find the password expiry date for a specific user, run this query:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = 'user_name';<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note: If the result is empty, it means the password does not have an expiry date set, which is typically configured as &#8220;unlimited&#8221; in the user&#8217;s profile.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">5. Check Password Change History<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You can check the history of password changes for an Oracle user by querying the USER$ and USER_HISTORY$ tables. The following query retrieves the password change dates for the specified user:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT A.NAME, B.PASSWORD_DATE AS \"Password Changed Date\"\nFROM SYS.USER$ A, SYS.USER_HISTORY$ B\nWHERE A.USER# = B.USER# \nAND A.NAME = 'HR'\nORDER BY B.PASSWORD_DATE;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">These SQL scripts allow you to efficiently retrieve important user account details in Oracle, providing valuable insights for security audits and account management.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, it is essential to monitor and manage user account details such as password changes, account creation, last login times, password expiry dates, and password change history. This post will walk you through the most useful SQL scripts to retrieve this information. 1. Check User Account Creation Date To check the account creation date [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-1113","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=1113"}],"version-history":[{"count":8,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1113\/revisions"}],"predecessor-version":[{"id":1578,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1113\/revisions\/1578"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=1113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=1113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=1113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}