查看锁表语句以及创建触发器

锁表

select sess.sid,

      sess.serial#,

      lo.oracle_username,

      lo.os_user_name,

      ao.object_name,

      lo.locked_mode

 from v$locked_object lo, dba_objects ao, v$session sess

where ao.object_id = lo.object_id

  and lo.session_id = sess.sid;

查看数据库引起锁表的SQL语句

SELECT A.USERNAME,

      A.MACHINE,

      A.PROGRAM,

      A.SID,

      A.SERIAL#,

      A.STATUS,

      C.PIECE,

      C.SQL_TEXT

 FROM V$SESSION A, V$SQLTEXT C

WHERE A.SID IN (SELECT DISTINCT T2.SID

                  FROM V$LOCKED_OBJECT T1, V$SESSION T2

                 WHERE T1.SESSION_ID = T2.SID)

  AND A.SQL_ADDRESS = C.ADDRESS(+)

ORDER BY C.PIECE;

杀掉锁表进程

alter system kill session '294,59748';--分别为SID和SERIAL#号

创建触发器

CREATE OR REPLACE TRIGGER tri_loan_into_interest

AFTER INSERT OR UPDATE ON loan_info

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO interest_info (lid,cid,lsum,status)

VALUES(:NEW.lid,:NEW.cid,:new.lsum,:new.status);

ELSIF UPDATING THEN

UPDATE interest_info SET status=:NEW.status WHERE lid=:OLD.lid;

END IF; END;