本文档风哥主要介绍Oracle ORA-28001密码过期相关知识，包括ORA-28001的概念、ORA-28001的常见原因、ORA-28001分析方法、ORA-28001错误分析配置、ORA-28001错误监控、ORA-28001错误故障处理等内容，由风哥教程参考Oracle官方文档Troubleshooting内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 ORA-28001的概念
Oracle ORA-28001是Oracle数据库的密码过期错误，表示用户密码已过期。ORA-28001错误通常发生在用户密码达到PASSWORD_LIFE_TIME限制时。ORA-28001错误需要更改密码才能继续使用账户。更多视频教程www.fgedu.net.cn
- 密码过期：用户密码已过期
- 生命周期限制：密码达到PASSWORD_LIFE_TIME限制
- 宽限期：密码过期后可能进入宽限期
- 必须更改：必须更改密码才能继续使用
- 安全策略：密码过期是安全策略的一部分
1.2 ORA-28001的常见原因
Oracle ORA-28001的常见原因：
- 密码生命周期到期：密码达到PASSWORD_LIFE_TIME限制
- 宽限期到期：密码宽限期PASSWORD_GRACE_TIME到期
- PROFILE配置：PROFILE配置了密码生命周期
- 管理员设置：管理员手动设置密码过期
- 安全策略：安全策略强制密码过期
- 密码未更改：用户长期未更改密码
- 默认配置：默认PROFILE配置了密码过期
1.3 ORA-28001分析方法
Oracle ORA-28001分析方法：
- 错误信息分析：分析错误信息含义
- 账户状态分析：分析用户账户状态
- PROFILE分析：分析用户PROFILE配置
- 密码过期时间分析：分析密码过期时间
- 宽限期分析：分析宽限期状态
- 告警日志分析：分析告警日志
Part02-生产环境规划与建议
2.1 ORA-28001错误处理规划
Oracle ORA-28001错误处理规划要点：
– 错误监控：监控ORA-28001错误发生
– 错误记录：记录ORA-28001错误信息
– 错误分析：分析ORA-28001错误原因
– 错误处理：处理ORA-28001错误问题
# 错误监控规划
– 监控工具：选择监控工具
– 监控频率：设置监控频率
– 监控范围：定义监控范围
– 告警配置：配置告警规则
# 错误记录规划
– 记录方式：选择记录方式
– 记录格式：定义记录格式
– 记录保留：设置记录保留时间
– 记录备份：备份错误记录
# 错误分析规划
– 分析方法：选择分析方法
– 分析工具：选择分析工具
– 分析流程：定义分析流程
– 分析报告：生成分析报告
# 错误处理规划
– 处理流程：定义处理流程
– 处理优先级：设置处理优先级
– 处理责任人：指定处理责任人
– 处理验证：验证处理结果
2.2 ORA-28001错误分析工具
Oracle ORA-28001错误分析工具：
- SQL*Plus：命令行工具
- SQL Developer：图形化工具
- Enterprise Manager：企业级管理工具
- DBA_USERS：用户账户状态视图
- DBA_PROFILES：PROFILE配置视图
- USER_PASSWORD_LIMITS：密码限制视图
– SQL*Plus：快速查询错误信息
– SQL Developer：图形化分析错误
– Enterprise Manager：集中管理错误
– DBA_USERS：查看用户账户状态
– DBA_PROFILES：查看PROFILE配置
– USER_PASSWORD_LIMITS：查看密码限制
2.3 ORA-28001错误处理最佳实践
Oracle ORA-28001错误处理最佳实践：
- 定期监控：定期监控ORA-28001错误
- 及时处理：及时处理ORA-28001错误
- 文档记录：记录错误处理过程
- 经验积累：积累错误处理经验
- 预防措施：制定预防措施
Part03-生产环境项目实施方案
3.1 ORA-28001错误分析配置
3.1.1 配置密码过期管理
SQL> select username, account_status, expiry_date, profile
from dba_users
where username = ‘SCOTT’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT EXPIRED 31-MAR-26 10:00:00 DEFAULT
# 2. 查看所有过期账户
SQL> select username, account_status, expiry_date, profile
from dba_users
where account_status like ‘%EXPIRED%’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT EXPIRED 31-MAR-26 10:00:00 DEFAULT
TEST EXPIRED(GRACE) 31-MAR-26 09:00:00 DEFAULT
# 3. 查看用户PROFILE配置
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_type = ‘PASSWORD’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_LIFE_TIME 180
DEFAULT PASSWORD_GRACE_TIME 7
DEFAULT PASSWORD_REUSE_TIME UNLIMITED
DEFAULT PASSWORD_REUSE_MAX UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
# 4. 更改用户密码
SQL> alter user scott identified by tiger;
User altered.
# 5. 验证账户状态
SQL> select username, account_status, expiry_date, profile
from dba_users
where username = ‘SCOTT’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT OPEN 27-SEP-26 10:00:00 DEFAULT
# 6. 测试用户登录
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show user
USER is “SCOTT”
# 7. 查看密码限制
SQL> select * from user_password_limits;
RESOURCE_NAME LIMIT
——————————– ———-
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION NULL
3.1.2 配置PROFILE密码策略
SQL> create profile secure_profile limit
failed_login_attempts 5
password_lock_time 1
password_life_time 90
password_grace_time 14
password_reuse_time 365
password_reuse_max 5;
Profile created.
# 2. 查看PROFILE配置
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘SECURE_PROFILE’
and resource_type = ‘PASSWORD’;
PROFILE RESOURCE_NAME LIMIT
————– ——————————– ———-
SECURE_PROFILE FAILED_LOGIN_ATTEMPTS 5
SECURE_PROFILE PASSWORD_LOCK_TIME 1
SECURE_PROFILE PASSWORD_LIFE_TIME 90
SECURE_PROFILE PASSWORD_GRACE_TIME 14
SECURE_PROFILE PASSWORD_REUSE_TIME 365
SECURE_PROFILE PASSWORD_REUSE_MAX 5
SECURE_PROFILE PASSWORD_VERIFY_FUNCTION NULL
# 3. 修改用户PROFILE
SQL> alter user scott profile secure_profile;
User altered.
# 4. 验证用户PROFILE
SQL> select username, profile from dba_users where username = ‘SCOTT’;
USERNAME PROFILE
———- ————–
SCOTT SECURE_PROFILE
# 5. 修改PROFILE密码生命周期
SQL> alter profile secure_profile limit
password_life_time unlimited;
Profile altered.
# 6. 验证PROFILE修改
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘SECURE_PROFILE’
and resource_name = ‘PASSWORD_LIFE_TIME’;
PROFILE RESOURCE_NAME LIMIT
————– ——————————– ———-
SECURE_PROFILE PASSWORD_LIFE_TIME UNLIMITED
# 7. 设置密码永不过期
SQL> alter profile default limit
password_life_time unlimited;
Profile altered.
# 8. 验证默认PROFILE
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_name = ‘PASSWORD_LIFE_TIME’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
3.2 ORA-28001错误监控
3.2.1 配置ORA-28001错误监控脚本
$ vi /home/oracle/scripts/monitor_ora28001.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ORA-28001错误监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
# 定义变量
ERROR_LOG=”/home/oracle/scripts/ora28001_errors.log”
EMAIL=”admin@fgedu.net.cn”
# 检查ORA-28001错误
check_ora28001_errors() {
local last_check_file=”/home/oracle/scripts/last_ora28001_check.txt”
local current_time=$(date +%s)
if [ -f “$last_check_file” ]; then
local last_check=$(cat “$last_check_file”)
local time_diff=$((current_time – last_check))
if [ $time_diff -lt 3600 ]; then
echo “Last check was $time_diff seconds ago, skipping…”
return 0
fi
fi
# 检查过期账户
local expired_accounts=$(sqlplus -s / as sysdba <
EOF
)
if [ -n “$expired_accounts” ]; then
echo “Found expired accounts:” >> “$ERROR_LOG”
echo “$expired_accounts” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
# 发送告警邮件
echo “ORA-28001 error detected: expired accounts found” | mail -s “ORA-28001 Alert” “$EMAIL”
# 分析过期账户
analyze_expired_accounts
fi
# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}
# 分析过期账户
analyze_expired_accounts() {
local expired_accounts=$(sqlplus -s / as sysdba <
EOF
)
echo “Expired Accounts Details:” >> “$ERROR_LOG”
echo “$expired_accounts” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}
# 主函数
main() {
check_ora28001_errors
echo “ORA-28001 error monitoring completed at $(date)” >> “$ERROR_LOG”
}
# 执行主函数
main
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ora28001.sh
# 3. 测试脚本
$ /home/oracle/scripts/monitor_ora28001.sh
# 4. 设置定期监控
$ crontab -e
# 每小时检查ORA-28001错误
0 * * * * /home/oracle/scripts/monitor_ora28001.sh >> /home/oracle/scripts/monitor_ora28001.log 2>&1
3.2.2 配置数据库监控
SQL> create or replace view expired_account_monitor as
select
username,
account_status,
expiry_date,
profile,
(select limit from dba_profiles p where p.profile = u.profile and resource_name = ‘PASSWORD_LIFE_TIME’) as password_life_time,
(select limit from dba_profiles p where p.profile = u.profile and resource_name = ‘PASSWORD_GRACE_TIME’) as password_grace_time,
case
when account_status = ‘EXPIRED’ then ‘PASSWORD_EXPIRED’
when account_status = ‘EXPIRED(GRACE)’ then ‘IN_GRACE_PERIOD’
else ‘NORMAL’
end as status_type
from dba_users u
where account_status like ‘%EXPIRED%’;
View created.
# 2. 查询过期账户状态
SQL> select * from expired_account_monitor;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE PASSWORD_LIFE_TIME PASSWORD_GRACE_TIME STATUS_TYPE
———- —————– ——————- ———- —————— ——————- —————
SCOTT EXPIRED 31-MAR-26 10:00:00 DEFAULT 180 7 PASSWORD_EXPIRED
TEST EXPIRED(GRACE) 31-MAR-26 09:00:00 DEFAULT 180 7 IN_GRACE_PERIOD
# 3. 创建密码即将过期监控视图
SQL> create or replace view password_expiring_soon as
select
username,
account_status,
expiry_date,
profile,
trunc(expiry_date – sysdate) as days_until_expiry,
(select limit from dba_profiles p where p.profile = u.profile and resource_name = ‘PASSWORD_LIFE_TIME’) as password_life_time
from dba_users u
where account_status = ‘OPEN’
and expiry_date is not null
and expiry_date < sysdate + 30
order by expiry_date;
View created.
# 4. 查询即将过期账户
SQL> select * from password_expiring_soon;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE DAYS_UNTIL_EXPIRY PASSWORD_LIFE_TIME
———- —————– ——————- ———- —————– ——————
HR OPEN 05-APR-26 10:00:00 DEFAULT 5 180
SH OPEN 10-APR-26 10:00:00 DEFAULT 10 180
OE OPEN 15-APR-26 10:00:00 DEFAULT 15 180
# 5. 创建ORA-28001错误告警
SQL> create or replace procedure ora28001_error_alert as
v_expired_count number;
v_expiring_soon_count number;
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 获取过期账户数量
select count(*) into v_expired_count
from dba_users
where account_status like ‘%EXPIRED%’;
— 获取即将过期账户数量
select count(*) into v_expiring_soon_count
from dba_users
where account_status = ‘OPEN’
and expiry_date is not null
and expiry_date < sysdate + 7;
if v_expired_count > 0 or v_expiring_soon_count > 0 then
dbms_output.put_line(‘Expired accounts: ‘ || v_expired_count);
dbms_output.put_line(‘Expiring soon accounts: ‘ || v_expiring_soon_count);
if v_expired_count > 0 then
dbms_output.put_line(‘— Expired Accounts —‘);
for rec in (select username, account_status, expiry_date from expired_account_monitor) loop
dbms_output.put_line(‘Username: ‘ || rec.username);
dbms_output.put_line(‘Status: ‘ || rec.account_status);
dbms_output.put_line(‘Expiry Date: ‘ || to_char(rec.expiry_date, ‘YYYY-MM-DD HH24:MI:SS’));
end loop;
end if;
if v_expiring_soon_count > 0 then
dbms_output.put_line(‘— Expiring Soon Accounts —‘);
for rec in (select username, expiry_date, days_until_expiry from password_expiring_soon where days_until_expiry < 7) loop
dbms_output.put_line('Username: ' || rec.username);
dbms_output.put_line(‘Expiry Date: ‘ || to_char(rec.expiry_date, ‘YYYY-MM-DD HH24:MI:SS’));
dbms_output.put_line(‘Days Until Expiry: ‘ || rec.days_until_expiry);
end loop;
end if;
dbms_output.put_line(‘Recommendation: Change passwords or adjust profile settings’);
else
dbms_output.put_line(‘No expired or expiring soon accounts found’);
end if;
end ora28001_error_alert;
/
Procedure created.
# 6. 执行告警存储过程
SQL> set serveroutput on
SQL> exec ora28001_error_alert;
Expired accounts: 2
Expiring soon accounts: 1
— Expired Accounts —
Username: SCOTT
Status: EXPIRED
Expiry Date: 2026-03-31 10:00:00
Username: TEST
Status: EXPIRED(GRACE)
Expiry Date: 2026-03-31 09:00:00
— Expiring Soon Accounts —
Username: HR
Expiry Date: 2026-04-05 10:00:00
Days Until Expiry: 5
Recommendation: Change passwords or adjust profile settings
PL/SQL procedure successfully completed.
# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA28001_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA28001_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=DAILY;INTERVAL=1’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
3.3 ORA-28001错误故障处理
3.3.1 ORA-28001错误处理
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for SCOTT
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)
# 分析步骤
# 1. 查看用户账户状态
SQL> select username, account_status, expiry_date, profile
from dba_users
where username = ‘SCOTT’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT EXPIRED 31-MAR-26 10:00:00 DEFAULT
# 2. 查看用户PROFILE配置
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_type = ‘PASSWORD’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_LIFE_TIME 180
DEFAULT PASSWORD_GRACE_TIME 7
# 3. 分析错误原因
# 错误代码：ORA-28001
# 错误信息：the password has expired
# 账户状态：EXPIRED
# 过期原因：密码达到PASSWORD_LIFE_TIME限制
# 4. 解决方案：更改密码
SQL> alter user scott identified by tiger;
User altered.
# 5. 验证账户状态
SQL> select username, account_status, expiry_date, profile
from dba_users
where username = ‘SCOTT’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT OPEN 27-SEP-26 10:00:00 DEFAULT
# 6. 验证问题解决
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show user
USER is “SCOTT”
# 7. 预防措施
# – 设置PASSWORD_LIFE_TIME为UNLIMITED
# – 定期检查即将过期账户
# – 配置密码过期告警
# – 提前通知用户更改密码
3.3.2 宽限期处理
SQL> select username, account_status, expiry_date, profile
from dba_users
where account_status = ‘EXPIRED(GRACE)’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
TEST EXPIRED(GRACE) 31-MAR-26 09:00:00 DEFAULT
# 2. 查看宽限期配置
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_name = ‘PASSWORD_GRACE_TIME’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT PASSWORD_GRACE_TIME 7
# 3. 在宽限期内更改密码
$ sqlplus test/fgfgfgtest123@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> alter user test identified by newpass123;
User altered.
# 4. 验证账户状态
SQL> select username, account_status, expiry_date
from dba_users
where username = ‘TEST’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE
———- —————– ——————-
TEST OPEN 27-SEP-26 10:00:00
# 5. 延长宽限期
SQL> alter profile default limit
password_grace_time 14;
Profile altered.
# 6. 验证宽限期修改
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_name = ‘PASSWORD_GRACE_TIME’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT PASSWORD_GRACE_TIME 14
# 7. 禁用密码过期
SQL> alter profile default limit
password_life_time unlimited;
Profile altered.
# 8. 验证密码生命周期
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_name = ‘PASSWORD_LIFE_TIME’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT PASSWORD_LIFE_TIME UNLIMITED
Part04-生产案例与实战讲解
4.1 ORA-28001错误分析案例
在生产环境中分析ORA-28001错误的完整案例：
4.1.1 场景描述
某企业生产数据库出现ORA-28001密码过期错误，需要分析错误原因并解决问题。
4.1.2 分析步骤
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
# 2. 查看用户账户状态
SQL> select username, account_status, expiry_date, profile
from dba_users
where username = ‘SCOTT’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT EXPIRED 31-MAR-26 10:00:00 DEFAULT
# 3. 查看PROFILE配置
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_type = ‘PASSWORD’;
PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT FAILED_LOGIN_ATTEMPTS 10
DEFAULT PASSWORD_LOCK_TIME 1
DEFAULT PASSWORD_LIFE_TIME 180
DEFAULT PASSWORD_GRACE_TIME 7
# 4. 分析错误原因
# 错误代码：ORA-28001
# 错误信息：the password has expired
# 账户状态：EXPIRED
# 过期原因：密码达到PASSWORD_LIFE_TIME限制（180天）
# 5. 解决方案：更改密码
SQL> alter user scott identified by tiger;
User altered.
# 6. 验证问题解决
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show user
USER is “SCOTT”
4.2 ORA-28001错误故障处理
在ORA-28001错误故障处理过程中的方法和技巧：
4.2.1 故障处理流程
# 1. 错误识别
# – 监控过期账户
# – 检查错误信息
# – 确认错误参数
# 2. 错误分析
# – 查看账户状态
# – 检查PROFILE配置
# – 分析过期时间
# 3. 错误处理
# – 更改密码
# – 调整PROFILE配置
# – 设置密码永不过期
# 4. 错误预防
# – 设置PASSWORD_LIFE_TIME为UNLIMITED
# – 定期检查即将过期账户
# – 制定预防措施
# 示例：ORA-28001错误处理
# 1. 错误识别
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-28001: the password has expired
# 2. 错误分析
SQL> select username, account_status, expiry_date, profile
from dba_users
where username = ‘SCOTT’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE PROFILE
———- —————– ——————- ———-
SCOTT EXPIRED 31-MAR-26 10:00:00 DEFAULT
# 3. 错误处理
SQL> alter user scott identified by tiger;
User altered.
# 4. 验证处理结果
$ sqlplus scott/tiger@FGEDUDB
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> show user
USER is “SCOTT”
# 5. 错误预防
# – 设置PASSWORD_LIFE_TIME为UNLIMITED
# – 定期检查即将过期账户
# – 配置密码过期告警
# – 提前通知用户更改密码
4.3 ORA-28001错误优化
优化ORA-28001错误处理配置的最佳实践：
4.3.1 优化密码策略管理
SQL> create or replace procedure optimize_password_policy as
v_expired_count number;
v_expiring_soon_count number;
begin
— 获取过期账户数量
select count(*) into v_expired_count
from dba_users
where account_status like ‘%EXPIRED%’;
— 获取即将过期账户数量（7天内）
select count(*) into v_expiring_soon_count
from dba_users
where account_status = ‘OPEN’
and expiry_date is not null
and expiry_date < sysdate + 7;
if v_expired_count > 0 or v_expiring_soon_count > 0 then
dbms_output.put_line(‘Expired accounts: ‘ || v_expired_count);
dbms_output.put_line(‘Expiring soon accounts: ‘ || v_expiring_soon_count);
— 显示过期账户
if v_expired_count > 0 then
dbms_output.put_line(‘— Expired Accounts —‘);
for rec in (
select username, account_status, expiry_date
from dba_users
where account_status like ‘%EXPIRED%’
) loop
dbms_output.put_line(‘Username: ‘ || rec.username);
dbms_output.put_line(‘Status: ‘ || rec.account_status);
dbms_output.put_line(‘Expiry Date: ‘ || to_char(rec.expiry_date, ‘YYYY-MM-DD HH24:MI:SS’));
end loop;
end if;
— 显示即将过期账户
if v_expiring_soon_count > 0 then
dbms_output.put_line(‘— Expiring Soon Accounts —‘);
for rec in (
select username, expiry_date, trunc(expiry_date – sysdate) as days_left
from dba_users
where account_status = ‘OPEN’
and expiry_date is not null
and expiry_date < sysdate + 7
) loop
dbms_output.put_line('Username: ' || rec.username);
dbms_output.put_line(‘Expiry Date: ‘ || to_char(rec.expiry_date, ‘YYYY-MM-DD HH24:MI:SS’));
dbms_output.put_line(‘Days Left: ‘ || rec.days_left);
end loop;
end if;
dbms_output.put_line(‘Recommendation: Change passwords or adjust profile settings’);
else
dbms_output.put_line(‘All accounts are normal’);
end if;
end optimize_password_policy;
/
Procedure created.
# 2. 执行优化存储过程
SQL> set serveroutput on
SQL> exec optimize_password_policy;
Expired accounts: 2
Expiring soon accounts: 1
— Expired Accounts —
Username: SCOTT
Status: EXPIRED
Expiry Date: 2026-03-31 10:00:00
Username: TEST
Status: EXPIRED(GRACE)
Expiry Date: 2026-03-31 09:00:00
— Expiring Soon Accounts —
Username: HR
Expiry Date: 2026-04-05 10:00:00
Days Left: 5
Recommendation: Change passwords or adjust profile settings
PL/SQL procedure successfully completed.
# 3. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘OPTIMIZE_PASSWORD_POLICY_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘OPTIMIZE_PASSWORD_POLICY’,
start_date => systimestamp,
repeat_interval => ‘FREQ=DAILY;INTERVAL=1’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
# 4. 批量更改过期账户密码
SQL> begin
for rec in (select username from dba_users where account_status like ‘%EXPIRED%’) loop
— 使用默认密码（生产环境应使用更安全的方式）
execute immediate ‘alter user ‘ || rec.username || ‘ identified by “NewPass123″‘;
dbms_output.put_line(‘Changed password for: ‘ || rec.username);
end loop;
end;
/
PL/SQL procedure successfully completed.
# 5. 验证账户状态
SQL> select username, account_status from dba_users where account_status like ‘%EXPIRED%’;
no rows selected
Part05-风哥经验总结与分享
5.1 ORA-28001错误总结
Oracle ORA-28001错误是密码过期错误，具有以下特点：
- 密码过期：用户密码已过期
- 生命周期限制：密码达到PASSWORD_LIFE_TIME限制
- 宽限期：密码过期后可能进入宽限期
- 必须更改：必须更改密码才能继续使用
- 安全策略：密码过期是安全策略的一部分
5.2 ORA-28001错误检查清单
Oracle ORA-28001错误检查清单：
- 错误识别：识别错误信息和参数
- 错误分析：分析账户状态和PROFILE配置
- 错误处理：处理错误问题
- 错误验证：验证处理结果
- 错误预防：制定预防措施
- 错误记录：记录处理过程
5.3 ORA-28001错误工具推荐
Oracle ORA-28001错误工具推荐：
- SQL*Plus：命令行工具
- SQL Developer：图形化工具
- Enterprise Manager：企业级管理工具
- DBA_USERS：用户账户状态视图
- DBA_PROFILES：PROFILE配置视图
- USER_PASSWORD_LIMITS：密码限制视图
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
