Quickly detects the lock wait method in the Oracle database and finds out the oracle database

Quickly detects the lock wait method in the Oracle database and finds out the oracle database

In a large database system, to ensure data consistency, the system locks data when operating the data in the database.

These locks include read-only locks, exclusive locks, shared exclusive locks, and other types, and each type has row-level locks (one record is locked at a time ), "Page-level locks" (one page is locked at a time, that is, the minimum allocable unit for storing records in the database) and "table-level locks" (locking the entire table ). If the row-level exclusive lock is set to "Row-level exclusive lock", other rows in the table can be updated or deleted by other users, if it is a table-level exclusive lock, all other users can only perform the select Operation on the table, but cannot modify or delete any records. After the program commits or rolls back the modifications, the locked resources are released, allowing other users to perform operations.

However, in some cases, for some special reasons in the program, the job is not submitted for a long time after the resource is locked; or, for user reasons, if the data to be modified is called up, if the request is not modified and submitted in time, it is placed aside; or the client crashes in the way of the client server, but the server does not detect it, causing the locked resources not to be released in time, other user operations may eventually be affected.

Therefore, it is a challenge for database administrators to quickly diagnose users who lock resources and solve their locking problems.
As database application systems become more and more complex, once lock resources are not released in a timely manner, a large number of users who operate on the same table will not be able to operate, thus affecting the use of the system. At this time, DBAs should solve the problem as quickly as possible. However, because the query statement "Get the user name waiting to lock the resource" is executed in Oracle 8.0.x

select a.username, a.sid, a.serial#, b.id1 from v$session a, v$lock b where a.lockwait = b.kaddr

Very slow (executed quickly in Oracle 7.3.4), and run the query statement "find and block user processes of other users ".

  select a.username, a.sid, a.serial#, b.id1   from v$session a, v$lock b   where b.id1 in      (select distinct e.id1       from v$session d, v$lock e       where d.lockwait = e.kaddr)    and a.sid = b.sid    and b.request = 0

Execution is also very slow. Therefore, it is usually necessary to set the status in the v $ session to "inactive" (not active) and the last operation time has exceeded 20 minutes (last_call_et> 20*60 seconds) to solve the problem.

However, this one-size-fits-all approach is actually "throwing off babies with dirty water ". Although some users' processes are "inactive" and haven't been active for a long time, they are in the lock wait status.

Therefore, I have come up with a solution. That is, by saving the relevant records in the v $ lock and v $ session views at the time of the problem in the self-created table and then querying the table, the speed is greatly improved, you can quickly find problems. After actual use, the effect is very good. After receiving a response from the user, you can find out the processes that affect other users due to resource lock in a few seconds and perform corresponding processing.

First, log on to the database as a dba (not necessarily system), create three basic tables: my_session, my_lock, and my_sqltext, and create corresponding indexes on the columns that will be queried. The statement is as follows:
Rem extracts the fields of interest from the v $ session view, creates a my_session table, and creates an index on the fields to be used in the query to speed up the query.

drop table my_session;create table my_sessionasselect a.username, a.sid, a.serial#,a.lockwait, a.machine,a.status,a.last_call_et,a.sql_hash_value,a.program from v$session a where 1=2 ;create unique index my_session_u1 on my_session(sid);create index my_session_n2 on my_session(lockwait);create index my_session_n3 on my_session(sql_hash_value);

Rem extracts fields from the v $ lock view, creates the my_lock table, and creates indexes on the fields to be used in the query to speed up the query.

drop table my_lock;create table my_lockasselect id1, kaddr, sid, request,type from v$lock where 1=2;create index my_lock_n1 on my_lock(sid);create index my_lock_n2 on my_lock(kaddr);

Rem extracts fields from the v $ sqltext view, creates a my_sqltext table, and creates an index on the fields to be used in the query to speed up the query.

drop table my_sqltext;create table my_sqltextasselect hash_value , sql_text from v$sqltext where 1=2;create index my_sqltext_n1 on my_sqltext ( hash_value);

Create an SQL script file so that it can be called directly from SQL * Plus if necessary. First, use the truncate table name command to delete the records in the table. The truncate command, instead of the delete command, is used because the record will be replayed during the execution of the delete command, which is slow and the space occupied by the index is not actually released, if the insert and delete operations are performed repeatedly, the space occupied by the index increases and the query speed slows down. The truncate command does not generate replays, And the execution speed is faster than that of delete, and the index space is released accordingly. After deleting a record, insert the relevant records in the three views into the three tables created by the user. Finally, the query results are displayed immediately because there is an index and the number of records is relatively small after the filter condition is inserted.

If the process that blocks other user processes is normal, you can notify the user to submit the process to release the lock resource. if the process is not normal, that is, if the status is "inactive" and Its last_call_et is a relatively long time, you can run the following statement to clear the process and the system will automatically roll back it, to release the locked resources.

alter system kill session 'sid, serial#'; 

The SQL script is as follows:

Set echo offset feedback offprompt 'delete old record ..... 'truncate table my_session; truncate table my_lock; truncate table my_sqltext; prompt' to get data ..... 'insert into my_sessionselect. username,. sid,. serial #,. lockwait,. machine,. status,. last_call_et,. SQL _hash_value,. program from v $ session a where nvl (. username, 'null') <> 'null; insert into my_lockselect id1, kaddr, sid, request, type from v $ lock; insert into my_sqltextselect hash_value, SQL _text from v $ sqltext s, my_session m where s. hash_value = m. SQL _hash_value; column username format a10column machine format a15column last_call_et format 99999 heading "Seconds" column sid format 9999 prompt "waiting for another user" select. sid,. serial #,. machine,. last_call_et,. username, B. id1 from my_session a, my_lock B where. lockwait = B. kaddr; prompt "Waiting user" select. sid,. serial #,. machine,. last_call_et,. username, B. b. type,. status, B. id1 from my_session a, my_lock B where B. id1 in (select distinct e. id1 from my_session d, my_lock e where d. lockwait = e. kaddr) and. sid = B. sid and B. request = 0; prompt "find its SQL" select. username,. sid,. serial #, B. id1, B. type, c. SQL _text from my_session a, my_lock B, my_sqltext c where B. id1 in (select distinct e. id1 from my_session d, my_lock e where d. lockwait = e. kaddr) and. sid = B. sid and B. request = 0 and c. hash_value =. SQL _hash_value;

The above ideas can also be used in other large database systems such as Informix, Sybase, and DB2. By using this script, you can greatly improve the situation of obtaining the current lock wait in the system, so as to promptly solve the lock wait problem in the database application system. In addition, because its program name and corresponding SQL statement have been extracted, you can record it later and hand it over to its developers for analysis and fundamentally solve it.

How to view deadlocks in ORACLE databases

SELECT substr (v $ lock. sid, 1, 4) "SID ",
Substr (username, 1, 12) "UserName ",
Substr (object_name, 1,25) "ObjectName ",
V $ lock. type "LockType ",
Decode (rtrim (substr (lmode, 1, 4 )),
'2', 'row-S (SS) ', '3', 'row-X (SX )',
'4', 'share', '5','s/Row-X (SSX )',
'6', 'clusive ', 'other') "LockMode ",
Substr (v $ session. program, 25-25) "ProgramName"
From v $ LOCK, SYS. DBA_OBJECTS, V $ SESSION
WHERE (OBJECT_ID = v $ lock. id1
AND v $ lock. sid = v $ session. sid
AND username IS NOT NULL
AND username not in ('sys ', 'system ')
And serial #! = 1 );

How can I unlock an Oracle database user account ?? How can I change the password ??

D: \ oracle \ ora92 \ bin> sqlplus/nolog

SQL * Plus: Release 9.2.0.1.0-Production on Thursday August 16 11:32:22 2007

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

SQL> conn/as sysdba
Connected.
SQL> alter user system identified by password;

The user has changed.

SQL> alter user sys identified by password;

The user has changed.

SQL> alter user system identified by manger;

The user has changed.

SQL> exit
From Oracle9i Enterprise Edition Release 9.2.0.1.0-Production
With the Partitioning, OLAP and Oracle Data Mining options
Disconnects from JServer Release 9.2.0.1.0-Production

D: \ oracle \ ora92 \ bin> sqlplus

SQL * Plus: Release 9.2.0.1.0-Production on Thursday August 16 11:40:37 2007

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Enter the User name: system
Enter the password:

Connect:
Oracle9i Enterprise Edition Release 9.2.0.1.0-Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0-Production

SQL> select instance_name from v $ instance;

INSTANCE_NAME
----------------
Reference: lanxing.javaeye.com/blog/112999