Oracle 查询数据库用户的密码过期时间

通过查询 dba_users 视图的 expiry_date 字段确认数据库用户的密码过期时间。

示例：

SQL> alter session set container=pdb;

Session altered.

SQL> col profile for a10
SQL> col resource_name for a30
SQL> col resource for a20
SQL> col limit for a10
SQL> select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

PROFILE    RESOURCE_NAME                  RESOURCE LIMIT      COM INH IMP
---------- ------------------------------ -------- ---------- --- --- ---
DEFAULT    PASSWORD_LIFE_TIME             PASSWORD 90         NO  NO  NO

SQL> col username for a10
SQL> col account_status for a10
SQL> col profile for a10
SQL> select username,account_status,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),profile from dba_users where account_status='OPEN';

USERNAME   ACCOUNT_ST TO_CHAR(EXPIRY_DATE PROFILE
---------- ---------- ------------------- ----------
SYS        OPEN                           DEFAULT
SYSTEM     OPEN                           DEFAULT
PDBADMIN   OPEN       2023-06-23 21:27:04 DEFAULT
MYUSER     OPEN       2024-05-05 16:24:28 DEFAULT

SQL> alter profile default limit password_life_time 180;

Profile altered.

SQL> select username,account_status,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),profile from dba_users where account_status='OPEN';

USERNAME   ACCOUNT_ST TO_CHAR(EXPIRY_DATE PROFILE
---------- ---------- ------------------- ----------
SYS        OPEN                           DEFAULT
SYSTEM     OPEN                           DEFAULT
PDBADMIN   OPEN       2023-09-21 21:27:04 DEFAULT
MYUSER     OPEN       2024-08-03 16:24:28 DEFAULT

SQL> alter profile default limit password_life_time unlimited;

Profile altered.

SQL> select username,account_status,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss'),profile from dba_users where account_status='OPEN';

USERNAME   ACCOUNT_ST TO_CHAR(EXPIRY_DATE PROFILE
---------- ---------- ------------------- ----------
SYS        OPEN                           DEFAULT
SYSTEM     OPEN                           DEFAULT
PDBADMIN   OPEN                           DEFAULT
MYUSER     OPEN                           DEFAULT

SQL> 

-- END --