Oracle lock table troubleshooting instance

Oracle lock table troubleshooting instance

One time a developer directly runs the delete table operating system on the database server during peak hours, resulting in no response for a long period of execution. Then, the SQLPLUS client is closed and the delete statement is run again, this causes the database to run slowly.

The query operation knows that it is caused by the lock table and is finally processed. This article only simulates the lock table processing process and can quickly handle the same problem next time.

Oracle Study Notes: sqlplus User Logon

Sqlplus ORA-01017 when logging on to Oracle: invalid username/password; logon denied Error

Feedback settings in SQLPLUS

1. view database wait events
If you do not know the cause, you can analyze it by waiting for the event.
SQL> select sid, EVENT from v $ session_wait where wait_class <> 'idle ';
SID EVENT
--------------------------------------------------------------------------
158 SQL * Net message to client
159 enq: TX-row lock contention
SQL>
 
2. Check whether the database has a lock
SQL> select * from v $ lock where block = 1;
 
3. query the lock to see who has locked the lock.
Note: a row with a BLOCK of 1 indicates that the resource is locked by it. REQUEST indicates that the lock is required.
SQL> SELECT sid, id1, id2, lmode, block, request, type
From v $ LOCK
WHERE id1 IN (SELECT id1 from v $ lock where lmode = 0)
Order by id1, request;
SID ID1 ID2 LMODE BLOCK REQUEST TY
--------------------------------------------------------------
153 655397 308 6 1 0 TX
157 655397 308 0 0 6 TX
 
4. query the SID, Serial #, user, start time of the lock table, and then you can kill the process.
SQL> select t1.sid, t1.serial #, t1.username, t1.logon _ time from v $ session t1, v $ locked_object t2 where t1.sid = t2.session _ id order by t1.logon _ time;
Sid serial # USERNAME LOGON_TIME
---------------------------------------------------------------------
153 12 ABC 09:19:33
157 106 ABC 09:54:24
 
5. You can further query SID, SPID, and other information.
SQL> select s. username, s. oSUSEr, s. sid, s. serial #, p. spid, s. program, s. STATUS
From v $ session s, v $ process p
Where s. paddr = p. addr and s. username is not null;
Username osuser sid serial # SPID PROGRAM STATUS
----------------------------------------------------------------------------------------------------------------------------------------------------
ABC oracle 153 12 4290 sqlplus @ oradb (TNS V1-V3) INACTIVE
SYS oracle 150 12 4417 sqlplus @ oradb (TNS V1-V3) ACTIVE
ABC oracle 157 119 4830 sqlplus @ oradb (TNS V1-V3) ACTIVE
You can either kill it in oracle or in the operating system. For statements whose status is killed, you can use the kill command of the operating system to kill it as quickly as possible.
Alter system kill session '2017, 12 ';
Alter system kill session '123 ';
Or in the operating system
Kill-9 4290
Kill-9 4830
 
6. query the SID and related resources of the locked table
Note: The tables and SQL statements to be locked must be manually processed in conjunction with the preceding query. You can also find the SQL statements by using the SID.
Set pagesize 999;
Set linesize 200;
Col PROGRAM for a25;
Col TERMINAL for a10;
SQL> select s. status, s. sid, s. serial #, p. spid,
S. last_call_et as exec_seconds, t. SQL _text as curr_ SQL
From gv $ session s, v $ process p, v $ instance I, v $ lock k, v $ sqltext t
Where s. paddr = p. addr
And s. type! = 'Background'
And s. lockwait = k. kaddr
And s. SQL _hash_value = t. hash_value
And s. username is not null;
Status sid serial # SPID EXEC_SECONDS CURR_ SQL
--------------------------------------------------------------------------------------------------------------------
ACTIVE 159 28 4072 update test_lock set name = 'aa4' where id = 1
SQL>