ORACLE Database query lock table statement sql Script , And delete lock information script ( Database development ETL、DBA necessary )

Preface

Because when some modifications or queries are performed on the database , The situation that caused the watch lock , This happens in many ways , For example, when querying, the index column fails , Row level locks are upgraded to table locks .

Tips ： The following is the main body of this article , The following cases can be used for reference

One 、Oracle Query table lock statement

1、 Check the database version

select * from v$version;

2、 database dba user ：system/oracle

grant create session to UserName

3、 Query the information of the lock table

    SELECT 
	L.SESSION_ID SID, 
	S.SERIAL#, 
	L.LOCKED_MODE,
	L.ORACLE_USERNAME,
	L.OS_USER_NAME,
	S.MACHINE, 
	S.TERMINAL, 
	O.OBJECT_NAME, 
	S.LOGON_TIME
　　FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S  
    WHERE L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    ORDER BY SID, S.SERIAL# ;
 

4、 Query out , Yes 2 Record of table lock ,IFB_WF_WAIT_ECDS This watch is locked

Two 、 Delete the information of the lock table kill

1、 Delete the... After the statement ’3493,35500’, Represents the queried SID、SERIAL Field

ALTER system KILL session '3493,35500'

2、 After performing , Lock table record deletion , Table lock release

summary

That's what we're going to talk about today , This article only briefly introduces Oracle Database query lock table 、 Delete the usage record of table lock session .