# 1. 查看当前锁信息
SQL> select
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
l.type,
l.lmode,
l.request,
o.object_name
from v$session s
join v$lock l on s.sid = l.sid
left join dba_objects o on l.id1 = o.object_id
where l.type in (‘TM’, ‘TX’)
order by s.sid;

SID SERIAL# USERNAME OSUSER MACHINE TY LMODE REQUEST OBJECT_NAME
———- ———- ———- ——— ———- — ———- ———- —————-
150 1234 SCOTT oracle dbserver TM 3 0 EMP
150 1234 SCOTT oracle dbserver TX 6 0

# 2. 查看锁定的对象
SQL> select
o.object_name,
o.object_type,
l.session_id,
l.locked_mode,
s.username,
s.osuser,
s.machine
from dba_objects o
join v$locked_object l on o.object_id = l.object_id
join v$session s on l.session_id = s.sid
order by o.object_name;

OBJECT_NAME OBJECT_TYPE SESSION_ID LOCKED_MODE USERNAME OSUSER MACHINE
—————————— ——————- ———- ———– ———- ——— ———-
EMP TABLE 150 3 SCOTT oracle dbserver

# 3. 查看阻塞会话
SQL> select
s1.sid as blocking_sid,
s1.serial# as blocking_serial,
s1.username as blocking_user,
s2.sid as waiting_sid,
s2.serial# as waiting_serial,
s2.username as waiting_user,
s2.event as wait_event
from v$session s1, v$session s2
where s1.sid in (
select blocking_session from v$session where blocking_session is not null
)
and s2.blocking_session = s1.sid;

BLOCKING_SID BLOCKING_SERIAL BLOCKING_USER WAITING_SID WAITING_SERIAL WAITING_USER WAIT_EVENT
———— ————— ————- ———– ————– ———— —————-
150 1234 SCOTT 160 5678 HR enq: TX – row lock contention

# 4. 查看DDL锁
SQL> select
s.sid,
s.serial#,
s.username,
d.name,
d.type,
d.mode_held
from v$session s
join dba_ddl_locks d on s.sid = d.session_id
where s.username is not null
order by s.sid;

SID SERIAL# USERNAME NAME TYPE MODE_HELD
———- ———- ———- —————————— ——— ———
150 1234 SCOTT EMP Table/Procedure/Type Exclusive

# 5. 设置DDL锁等待时间
SQL> alter session set ddl_lock_timeout = 60;

Session altered.

# 6. 查看DDL锁等待设置
SQL> show parameter ddl_lock_timeout;

NAME TYPE VALUE
———————————— ———– ——————————
ddl_lock_timeout integer 60

# 7. 设置系统级DDL锁等待时间
SQL> alter system set ddl_lock_timeout = 60 scope=both;

System altered.

# 8. 查看DML锁等待时间
SQL> show parameter dml_locks;

NAME TYPE VALUE
———————————— ———– ——————————
dml_locks integer 1416

# 1. 创建锁监控视图
SQL> create or replace view lock_monitor as
select
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.status,
s.last_call_et as seconds_in_wait,
l.type as lock_type,
l.lmode,
l.request,
l.ctime as time_held,
o.object_name,
o.object_type
from v$session s
join v$lock l on s.sid = l.sid
left join dba_objects o on l.id1 = o.object_id
where s.username is not null
and l.type in (‘TM’, ‘TX’, ‘UL’);

View created.

# 2. 查询锁监控信息
SQL> select * from lock_monitor where rownum <= 10;

SID SERIAL# USERNAME OSUSER MACHINE PROGRAM STATUS SECONDS_IN_WAIT LOCK_TYPE LMODE REQUEST TIME_HELD OBJECT_NAME OBJECT_TYPE
———- ———- ———- ——— ———- —————– ——— ————— ———- ———- ———- ———- —————————— ——————-
150 1234 SCOTT oracle dbserver sqlplus@dbserver ACTIVE 300 TX 6 0 300
150 1234 SCOTT oracle dbserver sqlplus@dbserver ACTIVE 300 TM 3 0 300 EMP TABLE

# 3. 创建阻塞会话监控视图
SQL> create or replace view blocking_session_monitor as
select
s1.sid as blocking_sid,
s1.serial# as blocking_serial,
s1.username as blocking_user,
s1.machine as blocking_machine,
s1.program as blocking_program,
s1.last_call_et as blocking_seconds,
s2.sid as waiting_sid,
s2.serial# as waiting_serial,
s2.username as waiting_user,
s2.machine as waiting_machine,
s2.program as waiting_program,
s2.event as wait_event,
s2.seconds_in_wait as wait_seconds
from v$session s1, v$session s2
where s1.sid in (
select blocking_session from v$session where blocking_session is not null
)
and s2.blocking_session = s1.sid;

View created.

# 4. 查询阻塞会话信息
SQL> select * from blocking_session_monitor;

BLOCKING_SID BLOCKING_SERIAL BLOCKING_USER BLOCKING_MACHINE BLOCKING_PROGRAM BLOCKING_SECONDS WAITING_SID WAITING_SERIAL WAITING_USER WAITING_MACHINE WAITING_PROGRAM WAIT_EVENT WAIT_SECONDS
———— ————— ————- —————- —————- —————- ———– ————– ———— —————- ————— ————————- ————
150 1234 SCOTT dbserver sqlplus@dbserver 300 160 5678 HR dbserver sqlplus@dbserver enq: TX – row lock contention 60

# 5. 创建事务监控视图
SQL> create or replace view transaction_monitor as
select
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
t.start_time,
t.status,
t.used_urec as undo_records,
t.used_ublk as undo_blocks,
round(t.used_ublk * 8 / 1024, 2) as undo_mb
from v$session s
join v$transaction t on s.saddr = t.ses_addr
where s.username is not null;

View created.

# 6. 查询事务监控信息
SQL> select * from transaction_monitor;

SID SERIAL# USERNAME MACHINE PROGRAM START_TIME STATUS UNDO_RECORDS UNDO_BLOCKS UNDO_MB
———- ———- ———- ———- —————– ——————– ——– ———— ———– ———-
150 1234 SCOTT dbserver sqlplus@dbserver 03/31/26 10:00:00 ACTIVE 100 10 0.08

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

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

# 检查ORA-00054错误
check_ora00054_errors() {
local last_check_file=”/home/oracle/scripts/last_ora00054_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

# 检查告警日志中的ORA-00054错误
local ora_errors=$(grep “ORA-00054” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log | tail -10)

if [ -n “$ora_errors” ]; then
echo “Found ORA-00054 errors:” >> “$ERROR_LOG”
echo “$ora_errors” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”

# 发送告警邮件
echo “ORA-00054 error detected: resource busy” | mail -s “ORA-00054 Alert” “$EMAIL”

# 分析锁错误
analyze_lock_errors
fi

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

# 分析锁错误
analyze_lock_errors() {
# 查询锁信息
local lock_info=$(sqlplus -s / as sysdba < exit
EOF
)

echo “Lock Information:” >> “$ERROR_LOG”
echo “$lock_info” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora00054_errors

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

# 执行主函数
main

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

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

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

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

# 1. 创建锁等待监控视图
SQL> create or replace view lock_wait_monitor as
select
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.event,
s.seconds_in_wait,
s.blocking_session,
s.wait_class,
sw.p1text,
sw.p1,
sw.p2text,
sw.p2,
sw.p3text,
sw.p3
from v$session s
join v$session_wait sw on s.sid = sw.sid
where s.wait_class = ‘Application’
and s.event like ‘%lock%’;

View created.

# 2. 查询锁等待信息
SQL> select * from lock_wait_monitor where rownum <= 10;

SID SERIAL# USERNAME MACHINE PROGRAM EVENT SECONDS_IN_WAIT BLOCKING_SESSION WAIT_CLASS P1TEXT P1 P2TEXT P2 P3TEXT P3
———- ———- ———- ———- —————– —————————— ————— —————- ———- ———- ———- ———- ———- ———-
160 5678 HR dbserver sqlplus@dbserver enq: TX – row lock contention 60 150 Applicatio name|mode 1415053318 usn<<16 | slot 196611 sequence 12345 # 3. 创建DDL锁监控视图 SQL> create or replace view ddl_lock_monitor as
select
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
d.name,
d.type,
d.mode_held,
d.mode_requested
from v$session s
join dba_ddl_locks d on s.sid = d.session_id
where s.username is not null;

View created.

# 4. 查询DDL锁信息
SQL> select * from ddl_lock_monitor where rownum <= 10;

SID SERIAL# USERNAME MACHINE PROGRAM NAME TYPE MODE_HELD MODE_REQUESTED
———- ———- ———- ———- —————– —————————— ——— —————– —————
150 1234 SCOTT dbserver sqlplus@dbserver EMP Table/Procedure/Type Exclusive None

# 5. 创建ORA-00054错误告警
SQL> create or replace procedure ora00054_error_alert as
v_lock_count number;
v_blocking_count number;
v_long_transaction_count number;
begin
— 获取锁数量
select count(*) into v_lock_count
from v$lock
where type in (‘TM’, ‘TX’);

— 获取阻塞会话数量
select count(*) into v_blocking_count
from v$session
where blocking_session is not null;

— 获取长时间事务数量
select count(*) into v_long_transaction_count
from v$transaction
where (sysdate – to_date(start_time, ‘MM/DD/YY HH24:MI:SS’)) * 24 * 60 > 30;

dbms_output.put_line(‘Active locks: ‘ || v_lock_count);
dbms_output.put_line(‘Blocked sessions: ‘ || v_blocking_count);
dbms_output.put_line(‘Long transactions (>30min): ‘ || v_long_transaction_count);

if v_blocking_count > 0 then
dbms_output.put_line(‘— Blocked Sessions —‘);
for rec in (select * from blocking_session_monitor) loop
dbms_output.put_line(‘Blocking: ‘ || rec.blocking_sid || ‘,’ || rec.blocking_serial || ‘ – ‘ || rec.blocking_user);
dbms_output.put_line(‘Waiting: ‘ || rec.waiting_sid || ‘,’ || rec.waiting_serial || ‘ – ‘ || rec.waiting_user);
dbms_output.put_line(‘Wait Event: ‘ || rec.wait_event);
dbms_output.put_line(‘Wait Seconds: ‘ || rec.wait_seconds);
end loop;

dbms_output.put_line(‘Recommendation: Commit/rollback blocking transaction or kill session’);
else
dbms_output.put_line(‘No blocking sessions found’);
end if;
end ora00054_error_alert;
/

Procedure created.

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

Active locks: 5
Blocked sessions: 1
Long transactions (>30min): 1
— Blocked Sessions —
Blocking: 150,1234 – SCOTT
Waiting: 160,5678 – HR
Wait Event: enq: TX – row lock contention
Wait Seconds: 60
Recommendation: Commit/rollback blocking transaction or kill session

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

# 问题现象
SQL> alter table emp add (phone varchar2(20));

alter table emp add (phone varchar2(20))
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

# 分析步骤

# 1. 查看锁定的对象
SQL> select
o.object_name,
l.session_id,
l.locked_mode,
s.username,
s.osuser,
s.machine,
s.program
from dba_objects o
join v$locked_object l on o.object_id = l.object_id
join v$session s on l.session_id = s.sid
where o.object_name = ‘EMP’;

OBJECT_NAME SESSION_ID LOCKED_MODE USERNAME OSUSER MACHINE PROGRAM
—————————— ———- ———– ———- ——— ———- —————-
EMP 150 3 SCOTT oracle dbserver sqlplus@dbserver

# 2. 查看会话详情
SQL> select sid, serial#, username, status, last_call_et, sql_id
from v$session
where sid = 150;

SID SERIAL# USERNAME STATUS LAST_CALL_ET SQL_ID
———- ———- ———- ——— ———— ————-
150 1234 SCOTT ACTIVE 300 7h3g8k2m9p1q

# 3. 查看会话执行的SQL
SQL> select sql_text
from v$sql
where sql_id = ‘7h3g8k2m9p1q’;

SQL_TEXT
————————————————————
update emp set sal = sal * 1.1 where deptno = 10

# 4. 分析错误原因
# 错误代码：ORA-00054
# 错误信息：resource busy and acquire with NOWAIT specified or timeout expired
# 锁定对象：EMP
# 锁定会话：SID=150, SERIAL#=1234
# 锁定用户：SCOTT
# 锁定程序：sqlplus@dbserver
# 错误原因：EMP表正被会话150锁定，无法执行DDL操作

# 5. 解决方案1：等待锁释放
SQL> alter session set ddl_lock_timeout = 60;

Session altered.

SQL> alter table emp add (phone varchar2(20));

Table altered.

# 6. 解决方案2：终止锁定会话
SQL> alter system kill session ‘150,1234’;

System altered.

SQL> alter table emp add (phone varchar2(20));

Table altered.

# 7. 解决方案3：联系用户提交事务
# 联系SCOTT用户提交或回滚事务

# 8. 预防措施
# – 在维护窗口执行DDL操作
# – 设置合理的DDL锁等待时间
# – 监控长时间运行的事务
# – 使用Online DDL选项

# 1. 创建索引时使用ONLINE选项
SQL> create index idx_emp_ename on emp(ename) online;

Index created.

# 2. 修改表结构时使用ONLINE选项
SQL> alter table emp add (phone varchar2(20)) online;

Table altered.

# 3. 查看DDL操作进度
SQL> select
sid,
serial#,
opname,
target,
sofar,
totalwork,
time_remaining
from v$session_longops
where opname like ‘%DDL%’;

SID SERIAL# OPNAME TARGET SOFAR TOTALWORK TIME_REMAINING
———- ———- —————————— —————————— ———- ———- ————–
150 1234 DDL SCOTT.EMP 5000 10000 60

# 4. 查看DDL锁等待
SQL> select
s.sid,
s.serial#,
s.username,
s.event,
s.seconds_in_wait,
s.blocking_session
from v$session s
where s.event like ‘%DDL%’;

SID SERIAL# USERNAME EVENT SECONDS_IN_WAIT BLOCKING_SESSION
———- ———- ———- —————————— ————— —————-
160 5678 HR library cache lock 120 150

# 5. 处理DDL锁等待
SQL> alter system kill session ‘150,1234’;

System altered.

# 6. 查看DDL操作状态
SQL> select * from dba_ddl_locks where session_id = 160;

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
———- —– —————————— ——— —————– —————
160 SCOTT EMP Table/Procedure/Type Exclusive None

# 7. 使用DDL_TIMEOUT参数
SQL> alter session set ddl_lock_timeout = 300;

Session altered.

SQL> alter table emp add (address varchar2(200));

Table altered.

# 8. 查看DDL操作历史
SQL> select
object_name,
object_type,
last_ddl_time,
status
from dba_objects
where object_name = ‘EMP’
order by last_ddl_time desc;

OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME STATUS
—————————— ——————- ——————- ———
EMP TABLE 2026-03-31 10:30:00 VALID