# 1. 查看用户账户状态
SQL> select username, account_status, lock_date, expiry_date, profile
from dba_users
where username = ‘SCOTT’;

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE PROFILE
———- —————– ——————- ——————- ———-
SCOTT LOCKED 31-MAR-26 10:00:00 30-APR-26 10:00:00 DEFAULT

# 2. 查看所有锁定账户
SQL> select username, account_status, lock_date, profile
from dba_users
where account_status like ‘%LOCKED%’;

USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE
———- —————– ——————- ———-
SCOTT LOCKED 31-MAR-26 10:00:00 DEFAULT
TEST LOCKED(TIMED) 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
DEFAULT PASSWORD_ROLLOVER_TIME UNLIMITED
DEFAULT INACTIVE_ACCOUNT_TIME UNLIMITED

# 4. 解锁用户账户
SQL> alter user scott account unlock;

User altered.

# 5. 验证账户状态
SQL> select username, account_status, lock_date, expiry_date, profile
from dba_users
where username = ‘SCOTT’;

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE PROFILE
———- —————– ——————- ——————- ———-
SCOTT OPEN 30-APR-26 10:00:00 DEFAULT

# 6. 重置用户密码
SQL> alter user scott identified by tiger;

User altered.

# 7. 测试用户登录
$ 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”

# 1. 创建新的PROFILE
SQL> create profile app_profile limit
failed_login_attempts 5
password_lock_time 1
password_life_time 90
password_grace_time 7
password_reuse_time 365
password_reuse_max 5;

Profile created.

# 2. 查看PROFILE配置
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘APP_PROFILE’
and resource_type = ‘PASSWORD’;

PROFILE RESOURCE_NAME LIMIT
———– ——————————– ———-
APP_PROFILE FAILED_LOGIN_ATTEMPTS 5
APP_PROFILE PASSWORD_LOCK_TIME 1
APP_PROFILE PASSWORD_LIFE_TIME 90
APP_PROFILE PASSWORD_GRACE_TIME 7
APP_PROFILE PASSWORD_REUSE_TIME 365
APP_PROFILE PASSWORD_REUSE_MAX 5
APP_PROFILE PASSWORD_VERIFY_FUNCTION NULL
APP_PROFILE PASSWORD_ROLLOVER_TIME UNLIMITED
APP_PROFILE INACTIVE_ACCOUNT_TIME UNLIMITED

# 3. 修改用户PROFILE
SQL> alter user scott profile app_profile;

User altered.

# 4. 验证用户PROFILE
SQL> select username, profile from dba_users where username = ‘SCOTT’;

USERNAME PROFILE
———- ———-
SCOTT APP_PROFILE

# 5. 修改PROFILE配置
SQL> alter profile app_profile limit
failed_login_attempts 10
password_lock_time unlimited;

Profile altered.

# 6. 验证PROFILE修改
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘APP_PROFILE’
and resource_name in (‘FAILED_LOGIN_ATTEMPTS’, ‘PASSWORD_LOCK_TIME’);

PROFILE RESOURCE_NAME LIMIT
———– ——————————– ———-
APP_PROFILE FAILED_LOGIN_ATTEMPTS 10
APP_PROFILE PASSWORD_LOCK_TIME UNLIMITED

# 7. 删除PROFILE
SQL> drop profile app_profile cascade;

Profile dropped.

# 1. 创建ORA-28000错误监控脚本
$ vi /home/oracle/scripts/monitor_ora28000.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-28000错误监控脚本

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/ora28000_errors.log”
EMAIL=”admin@fgedu.net.cn”

# 检查ORA-28000错误
check_ora28000_errors() {
local last_check_file=”/home/oracle/scripts/last_ora28000_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 locked_accounts=$(sqlplus -s / as sysdba < exit
EOF
)

if [ -n “$locked_accounts” ]; then
echo “Found locked accounts:” >> “$ERROR_LOG”
echo “$locked_accounts” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”

# 发送告警邮件
echo “ORA-28000 error detected: locked accounts found” | mail -s “ORA-28000 Alert” “$EMAIL”

# 分析锁定账户
analyze_locked_accounts
fi

# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}

# 分析锁定账户
analyze_locked_accounts() {
local locked_accounts=$(sqlplus -s / as sysdba < exit
EOF
)

echo “Locked Accounts Details:” >> “$ERROR_LOG”
echo “$locked_accounts” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora28000_errors

echo “ORA-28000 error monitoring completed at $(date)” >> “$ERROR_LOG”
}

# 执行主函数
main

# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ora28000.sh

# 3. 测试脚本
$ /home/oracle/scripts/monitor_ora28000.sh

# 4. 设置定期监控
$ crontab -e

# 每小时检查ORA-28000错误
0 * * * * /home/oracle/scripts/monitor_ora28000.sh >> /home/oracle/scripts/monitor_ora28000.log 2>&1

# 1. 创建锁定账户监控视图
SQL> create or replace view locked_account_monitor as
select
username,
account_status,
lock_date,
expiry_date,
profile,
(select limit from dba_profiles p where p.profile = u.profile and resource_name = ‘FAILED_LOGIN_ATTEMPTS’) as failed_login_attempts,
(select limit from dba_profiles p where p.profile = u.profile and resource_name = ‘PASSWORD_LOCK_TIME’) as password_lock_time
from dba_users u
where account_status like ‘%LOCKED%’;

View created.

# 2. 查询锁定账户状态
SQL> select * from locked_account_monitor;

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD_LOCK_TIME
———- —————– ——————- ——————- ———- ——————– —————–
SCOTT LOCKED 31-MAR-26 10:00:00 30-APR-26 10:00:00 DEFAULT 10 1
TEST LOCKED(TIMED) 31-MAR-26 09:00:00 30-APR-26 09:00:00 DEFAULT 10 1

# 3. 创建登录失败监控视图
SQL> create or replace view login_failure_monitor as
select
userid,
userhost,
terminal,
timestamp,
action_name,
returncode,
(select count(*) from dba_audit_session a2 where a2.userid = a1.userid and a2.returncode = 1017) as failed_count
from dba_audit_session a1
where returncode = 1017
and timestamp > sysdate – 1
order by timestamp desc;

View created.

# 4. 查询登录失败记录
SQL> select * from login_failure_monitor where rownum <= 10;

USERID USERHOST TERMINAL TIMESTAMP ACTION_NAME RETURNCODE FAILED_COUNT
——– —————– ———— ——————- ————— ———- ————
SCOTT workstation1 pts/0 31-MAR-26 10:00:00 LOGON 1017 5
TEST workstation2 pts/1 31-MAR-26 09:00:00 LOGON 1017 3

# 5. 创建ORA-28000错误告警
SQL> create or replace procedure ora28000_error_alert as
v_locked_count number;
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 获取锁定账户数量
select count(*) into v_locked_count
from dba_users
where account_status like ‘%LOCKED%’;

if v_locked_count > 0 then
— 发送告警
dbms_output.put_line(‘Locked accounts count: ‘ || v_locked_count);

for rec in (select username, account_status, lock_date, profile from locked_account_monitor) loop
dbms_output.put_line(‘Username: ‘ || rec.username);
dbms_output.put_line(‘Status: ‘ || rec.account_status);
dbms_output.put_line(‘Lock Date: ‘ || to_char(rec.lock_date, ‘YYYY-MM-DD HH24:MI:SS’));
dbms_output.put_line(‘Profile: ‘ || rec.profile);
dbms_output.put_line(‘—‘);
end loop;

dbms_output.put_line(‘Recommendation: Unlock accounts or check security policy’);
else
dbms_output.put_line(‘No locked accounts found’);
end if;
end ora28000_error_alert;
/

Procedure created.

# 6. 执行告警存储过程
SQL> set serveroutput on
SQL> exec ora28000_error_alert;

Locked accounts count: 2
Username: SCOTT
Status: LOCKED
Lock Date: 2026-03-31 10:00:00
Profile: DEFAULT
—
Username: TEST
Status: LOCKED(TIMED)
Lock Date: 2026-03-31 09:00:00
Profile: DEFAULT
—
Recommendation: Unlock accounts or check security policy

PL/SQL procedure successfully completed.

# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA28000_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA28000_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=HOURLY;INTERVAL=1’,
enabled => true
);
end;
/

PL/SQL procedure successfully completed.

# 问题现象
$ 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-28000: the account is locked

# 分析步骤

# 1. 查看用户账户状态
SQL> select username, account_status, lock_date, expiry_date, profile
from dba_users
where username = ‘SCOTT’;

USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE PROFILE
———- —————– ——————- ——————- ———-
SCOTT LOCKED 31-MAR-26 10:00:00 30-APR-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. 查看登录失败记录
SQL> select userid, userhost, terminal, timestamp, returncode
from dba_audit_session
where userid = ‘SCOTT’
and returncode = 1017
order by timestamp desc;

USERID USERHOST TERMINAL TIMESTAMP RETURNCODE
——– —————– ———— ——————- ———-
SCOTT workstation1 pts/0 31-MAR-26 10:00:00 1017
SCOTT workstation1 pts/0 31-MAR-26 09:59:00 1017
SCOTT workstation1 pts/0 31-MAR-26 09:58:00 1017

# 4. 分析错误原因
# 错误代码：ORA-28000
# 错误信息：the account is locked
# 账户状态：LOCKED
# 锁定原因：多次登录失败

# 5. 解决方案：解锁账户
SQL> alter user scott account unlock;

User altered.

# 6. 重置密码（可选）
SQL> alter user scott identified by tiger;

User altered.

# 7. 验证问题解决
$ 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”

# 8. 预防措施
# – 增加FAILED_LOGIN_ATTEMPTS限制
# – 配置密码复杂度验证函数
# – 定期检查锁定账户
# – 配置账户锁定告警

# 1. 查看当前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

# 2. 修改PROFILE配置
SQL> alter profile default limit
failed_login_attempts unlimited
password_lock_time unlimited;

Profile altered.

# 3. 验证PROFILE修改
SQL> select profile, resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’
and resource_name in (‘FAILED_LOGIN_ATTEMPTS’, ‘PASSWORD_LOCK_TIME’);

PROFILE RESOURCE_NAME LIMIT
———- ——————————– ———-
DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED
DEFAULT PASSWORD_LOCK_TIME UNLIMITED

# 4. 创建密码复杂度验证函数
SQL> create or replace function verify_function
(
username varchar2,
password varchar2,
old_password varchar2
) return boolean is
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
begin
digitarray := ‘0123456789’;
chararray := ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
punctarray := ‘!”#$%&()”*+,-/:;<=>?_’;

— 检查密码长度
if length(password) < 8 then raise_application_error(-20001, 'Password length less than 8');
end if;

— 检查是否包含数字
isdigit := false;
for i in 1..length(password) loop
for j in 1..length(digitarray) loop
if substr(password, i, 1) = substr(digitarray, j, 1) then
isdigit := true;
exit;
end if;
end loop;
end loop;

if not isdigit then
raise_application_error(-20002, ‘Password must contain at least one digit’);
end if;

— 检查是否包含字母
ischar := false;
for i in 1..length(password) loop
for j in 1..length(chararray) loop
if substr(password, i, 1) = substr(chararray, j, 1) then
ischar := true;
exit;
end if;
end loop;
end loop;

if not ischar then
raise_application_error(-20003, ‘Password must contain at least one letter’);
end if;

return true;
end;
/

Function created.

# 5. 应用密码复杂度验证函数
SQL> alter profile default limit
password_verify_function verify_function;

Profile altered.

# 6. 验证密码复杂度
SQL> alter user scott identified by simple;

alter user scott identified by simple
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8

SQL> alter user scott identified by Simple123;

User altered.

# 7. 测试用户登录
$ sqlplus scott/Simple123@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”