解决 Oracle 表锁

程序更新大量数据的时候, 由于时间太长, 页面刷新又重新提交了一次, 结果后续的任何变动都直接卡住, 一开始以为是代码问题, 使用 jstack 命令导出了当前堆栈, 结果发现有重复的语句卡在 update 上了, 看 sql 语句, 发现就是正常的 update, 那问题大概率就是出在表被锁身上了. 于是搜刮了一下 oracle 的方面的资料, 顺便也记录一下

步骤

1. 查询锁记录

SELECT b.owner, -- 数据表的所有者用户
       b.object_name, -- 被锁住的表名
       a.session_id,
       a.locked_mode, -- 锁级别
       c.username,
       c.sid ||',' || c.serial# sesh, -- session
       c.sid,
       c.serial#,
       c.logon_time
FROM v$locked_object a
    JOIN dba_objects b ON b.object_id = a.object_id
    JOIN v$session c ON a.session_id = c.sid
ORDER BY c.logon_time;

锁级别分为6级:
1 级锁有: select
2 级锁有: select for update, lock for update, lock row share
3 级锁有: insert, update, delete, lock row exclusive
4 级锁有: create index, lock share
5 级锁有: lock share row exclusive
6 级锁有: alter table, drop table, drop Index, truncate table, lock exclusive

2. 终止会话

alter system kill session '${sid},${serial#}';

• 出现 ORA-00031 错误解决办法: 后加 immediate

alter system kill session '41,29827' immediate;

Oracle 版本

本文使用的 Oracle 版本为 11g

• 查看 Oracle 版本:

SELECT * FROM v$version;

Oracle 报错

ORA-22859: invalid modification of columns

ALTER TABLE table_a MODIFY field_b NVARCHAR2(200);

在Oracle数据库中，对于某些列类型（如CLOB、BLOB、LONG等），直接修改列的数据类型是不允许的。这是由于这些列类型的存储方式和处理方式与普通列类型不同。

解决办法: 先删除字段, 后增加字段

ALTER TABLE table_a DROP COLUMN field_b;
ALTER TABLE table_a ADD field_b NVARCHAR2(200);

如果有备份需求, 可以更新字段 (不建议)

cast() 函数 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm
When using CAST(…) in a select, you need to specify size e.g. VARCHAR2(20)

-- 带备份
ALTER TABLE table_a RENAME COLUMN field_b to field_b_1;
ALTER TABLE table_a ADD field_b NVARCHAR2(200);
UPDATE table_a SET field_b = CAST(field_b_1 AS NVARCHAR2(200));
ALTER TABLE table_a DROP COLUMN field_b_1;

---

(完)