我对一个表的flag字段(char(1))进行update操作,就几十条记录。却停在那里。执行如下语句,找不到有对该表进行操作的session。不知什么进程锁了该表。请问是什么原因,怎么解决?谢谢!
select a.sid,b.sql_text from v$session a, v$sql b where a.sql_address=b.address;
select a.sid,b.sql_text from v$session a, v$sql b where a.sql_address=b.address;
v$locked_object,V$session可以把该 session杀掉。
select sid,serial# from v$session where username ='XXXX'
把得到的sid,serial#号替换到下面的语句中:
alter system kill session 'SID,SERIAL#'然后执行。
select a.owner, a.object_name, b.xidusn, b.xidslot, b.xidsqn, b.session_id, b.oracle_username, b.process
b.locked_mode, c.machine, c.status, c.server, c.sid, c.serial#, c.program from all_objects a, v$locked_onbject b, v$session c
where a.object_id=b.object_id and b.process=c.process可是没查到这张表。很奇怪。还有没别的办法?
查查归档模式,是不是手工归档,如果是的,运行SQL>alter system archive log current;不知道是不是这个原因了,你先看看先,如果确实是的话,最后记得启动自动归档 SQL>alter system archive log start;
GES:Potential blocker(pid=26906) on source TX-0001002D-000277F5;
在网上查了一些资料,说是死锁引起的。但是怎么避免呢?
CREATE OR REPLACE TRIGGER "TG_APAS_INFO_D" AFTER DELETE On newapas.apas_info FOR EACH ROW
DECLARE
IS_EXIST_I NUMBER; --定义在临时表I里面unid为指定unid的记录数
IS_EXIST_D NUMBER; --定义在临时表D里面unid为指定unid的记录数
BEGIN
IF (user != 'DATATEST') THEN
--判断在临时表I里面有没有要编号为unid的记录数,如果在临时表I里面已经有记录的话,执行删除操作
SELECT COUNT(*) INTO IS_EXIST_I FROM newapas.lw_apas_info_i lw_i
WHERE lw_i.unid = :old.unid; IF (IS_EXIST_I != 0) THEN
DELETE FROM newapas.lw_apas_info_i lw_i WHERE lw_i.unid = :old.unid;
END IF; --********************************************************************************-- --判断在临时表D里面有没有编号为unid的记录,如果有的话,删除后原记录后,增加一条待删除的记录
SELECT COUNT(*) INTO IS_EXIST_D FROM newapas.lw_apas_info_d lw_d
WHERE lw_d.unid = :old.unid; IF (IS_EXIST_D != 0) THEN
DELETE FROM newapas.lw_apas_info_d lw_d WHERE lw_d.unid = :old.unid;
END IF; --********************************************************************************-- --插入临时表D记录
INSERT INTO newapas.lw_apas_info_d VALUES(
:old.UNID,
:old.SERVICEID,
:old.SERVICENAME,
:old.SERVICETYPE,
:old.PROJID,
:old.PROJPWD,
:old.PROMISEDAY,
:old.PROJECTNAME,
:old.APPLYNAME,
:old.MOBILEPHONE,
:old.TELEPHONE,
:old.POSTCODE,
:old.ADDRESS,
:old.LEADERMAN,
:old.CONTACTMAN,
:old.PROXYMAN,
:old.FORMID,
:old.APPLYFROM,
:old.CREATETIME,
:old.CUSERUNID,
:old.CUSERNAME,
:old.RECEIVEUSER,
:old.RECEIVEDEPTID,
:old.RECEIVEDEPT,
:old.RECEIVEUSERID,
:old.DELETEFLAG,
:old.PROMISEENDDAY,
:old.TRANSACTTIME,
:old.HANDLESTATE,
:old.EFFISTATE,
:old.CURSTIME,
:old.MEMO,
:old.ACCTIME,
:old.EFFIVALUE,
:old.INFOTYPE,
:old.PARENTID,
:old.NODETIME,
:old.EMAIL,
:old.ISIMPORT,
:old.FUNDSOURCE,
:old.PRJPROPERTY,
:old.BUILDPROPERTY,
:old.BUILDSCALE,
:old.BUILDADDRESS,
:old.USEDAREA,
:old.INVESTMENT,
:old.ACCEPTMOBILE,
:old.MODIFYSIGN,
:old.IDCARD,
'0');
END IF;
END;其他操作该表的代码:
update newapas.lw_apas_info_d set flag='1';
2、kill 进程