ORACLE locks on the table and unlock Summary

2011-05-20  来源:本站原创  分类:Database  人气:76 

Summary 1: Oracle's lock and unlock the table
select
s.username,
decode (l.type, 'tm', 'table lock', 'tx', 'row lock', null) lock_level,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial #,
s.terminal,
s.machine,
s.program,
s.osuser
from v $ session s, v $ lock l, dba_objects o
where l.sid = s.sid
and l.id1 = o.object_id (+)
and s.username is not null;

- Kill session statement
alter system kill session'50, 492 ';
- The following table for the relevant
SELECT * FROM v $ lock;
SELECT * FROM v $ sqlarea;
SELECT * FROM v $ session;
SELECT * FROM v $ process;
SELECT * FROM v $ locked_object;
SELECT * FROM all_objects;
SELECT * FROM v $ session_wait;
- 1. Find out information on locking the session object and the object name is locked
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 #;
- 2. Found the lock table session sid, serial #, os_user_name, machine name, terminal and execute the statement
- More than the above period and action sql_text
SELECT l.session_id sid, s.serial #, l.locked_mode, l.oracle_username, s.user #,
l.os_user_name, s.machine, s.terminal, a.sql_text, a.action
FROM v $ sqlarea a, v $ session s, v $ locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial #;
- 3. Found locked table sid, serial #, os_user_name, machine_name, terminal, lock type, mode
SELECT s.sid, s.serial #, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v $ session s, v $ lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

This statement will find all the DML statements in the database generated by the lock can also be found
In fact, any DML statements had two locks, one lock, one row lock.
Lock command to kill
alter system kill session 'sid, serial #'
SELECT / * + rule * / s.username,
decode (l.type, 'TM', 'TABLE LOCK',
'TX', 'ROW LOCK',
NULL) LOCK_LEVEL,
o.owner, o.object_name, o.object_type,
s.sid, s.serial #, s.terminal, s.machine, s.program, s.osuser
FROM v $ session s, v $ lock l, dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id (+)
AND s.username is NOT NULL
If the lock wait, we might even want to know who is who caused the table lock wait can query the following statement to the who locked the table, and who is waiting.
These results is a tree, if the child node, then there is waiting to happen.
If you want to know the lock out which rollback segments, can also be linked to the V $ rollname, which xidusn USN rollback is
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
select lpad ('', decode (l.xidusn, 0,3,0)) | | l.oracle_username user_name,
o.owner, o.object_name, o.object_type, s.sid, s.serial #
from v $ locked_object l, dba_objects o, v $ session s
where l.object_id = o.object_id
and l.session_id = s.sid
order by o.object_id, xidusn desc

Summary 2: a useful search script:

column sid format 999;
column b format 9;
column object_name format a30;
column locktype format a20;
select v $ lock.sid,
decode (v $ lock.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL / SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
rtrim (owner) | | '.' | | object_name object_name,
decode (lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S / Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode (request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S / Row-X',
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v $ lock, all_objects
where sid> 6
and v $ lock.id1 = all_objects.object_id;

Find a table lock belongs sid.
alter system kill session 'sid, serial #';
To

select object_id, session_id, serial #, oracle_username, os_user_name, s.process
from v $ locked_object a, v $ session s
where a.session_id = s.sid;
Then lock the object identified by alter system kill session 'sid, serial #';

Knowledge Point 3:

LOCK TABLE
Syntax:
LOCK TABLE table_1 [, table_2, ..., table_n] IN lock_mode MODE
NOWAIT
Variables:
table_1 ,..., table_n: a series you want to use the LOCK TABLE statement to lock the database tables.
lock_mode: For a database table you want to set the lock mode. You can lock mode from the following to choose one.
  EXCLUSIVE
  SHARE ROW EXCLUSIVE
  SHARE
  SHARE UPDATE
  ROW SHARE
  ROW EXCLUSIVE
NOWAIT: Oracle will not wait to lock the given Table (s), if the Table (s) is (are) not
available
Example:
SQL
LOCK TABLE loan IN SHARE MODE;
LOCK TABLE region IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE acct IN SHARE UPDATE MODE;
LOCK TABLE bank IN ROW EXCLUSIVE MODE NOWAIT;
LOCK TABLE user IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE branch IN ROW SHARE MODE NOWAIT;

commit
/

相关文章
  • ORACLE locks on the table and unlock Summary 2011-05-20

    Summary 1: Oracle's lock and unlock the table select s.username, decode (l.type, 'tm', 'table lock', 'tx', 'row lock', null) lock_level, o.owner, o.object_name, o.object_type, s.sid, s.serial #, s.terminal, s.machine, s.program, s.osuser from v $ ses

  • Oracle locks Chapter 3 Table partitioning 2011-05-03

    Chapter 2 of Oracle query Oracle data types, functions Chapter 4 Oracle synonyms, sequences, views, indexes Chapter 3, Oracle locks, table partitioning 1, the technical objectives Understand the concept of lock Use table partitioning 2, the concept o

  • Oracle uses a temporary table in the summary 2010-11-11

    1 Syntax In Oracle, you can create two temporary tables: 1) session-specific temporary table CREATE GLOBAL TEMPORARY () ON COMMIT PRESERVE ROWS; 2) transaction-specific temporary table CREATE GLOBAL TEMPORARY () ON COMMIT DELETE ROWS; CREATE GLOBAL T

  • Oracle table to unlock 2010-12-07

    Reprinted: Oracle table to unlock http://toyota2006.javaeye.com/blog/599255 Other information: ORACLE locks on the table and unlock Summary http://linux.net527.cn/fuwuqiyingyong/Oracle/2010/1103/30162.html Table 1 to obtain the information locked SEL

  • Oracle View the index table space 2011-07-01

    Quote Summary: Oracle View the index tablespace, Oracle View the index table space statement, including the view table space usage, see the library database object, see the database version to check the creation date and archiving database, query the

  • oracle database, view the table space usage 2010-03-29

    oracle database, view the table space usage; oracle tablespace should always see what the situation is generally idle too low when the ratio should consider increasing the table to see space. See as follows SQL: Method 1: select dbf.tablespace_name,

  • Oracle to mysql migration project a small summary 2010-03-12

    1, update the table of the different oracle <update parameterClass="map"> update IP_GN_BAXX_GBXX_LS <dynamic prepend="set"> <isNotNull prepend="," property="provinceSystemId"> SJXT_ID = # provinceS

  • Oracle in a temporary table (working with real table) 2010-06-16

    1 Introduction At present, all supporting platform using oracle as a database application, most of the relatively large amount of data the system, that is, the amount of data table under normal circumstances are 1 million more than the amount of data

  • (Transfer) Oracle Application of temporary table 2010-07-22

    http://hi.baidu.com/edeed/blog/item/6d6e2834a71d113f5bb5f5ab.html Oracle Application of temporary table 2007-12-27 13:56 I do projects in recent years less use of temporary tables Temporary Table, in fact Temp Table or can compare a wide range of app

  • Flexible use of Oracle's dual virtual table 2010-09-25

    dual is a virtual table, select the syntax rules used to form, oracle dual which is always to ensure that only one record. We can use it to do many things, as follows: 1, see the current user can execute the following statement in SQL Plus select use

  • Have installed Oracle Database, to the scott account unlock method 2010-10-14

    Reprinted from: http://wangjunwj.javaeye.com/blog/610705 Bahrain the Oracle 10g database and forgot to unlock the scott account. Then in the sql plus tools in (Start | All Programs | <Oracle-Home> | Application Development | SQL Plus), can also give

  • Detailed Oracle variety of connection table 2011-07-13

    This article describes the Oracle database table in a variety of connectivity options, including the inner joins, outer join, since the connection and so on. We want to help. 1 in connection (natural connection) 2 outer joins (1) left outer join (on

  • PLSQL Developer oracle 10g create Delete table space, renamed 2011-07-20

    Through pl / sql login to the Oracle database, then execute the menu: File / New / command window, open a command window and then in the command window, execute the script to create and delete table space to create the table space create tablespace d

  • Oracle query the user table statements for all 2011-07-29

    select * from user_tab_privs query of the current user privileges. The following statement is used to introduce you to achieve all of the Oracle query the user table, if you are interested in oracle query, then, worth a look. select * from all_tab_co

  • sql query for all database Table name field, summary table 2010-12-16

    ms sql server 1, check all the tables select [id], [name] from [sysobjects] where [type] = 'u' order by [name] 2, all database queries 3, select [name] from [sysdatabases] order by [name] Fields in the query table select [name] from [syscolumns] wher

  • View user oracle database name and table space (turn) 2011-03-28

    View user's database name SELECT NAME FROM V $ DATABASE; See the table space name: select username, default_tablespace from dba_users; Oracle Database creates the table space, create a user-specified table space 2009-01-06 21:11 / / Create a temporar

  • oracle query the user table 2011-04-27

    ◆ Oracle query the user table space: select * from user_all_tables ◆ Oracle query all the functions and storage: select * from user_source ◆ Oracle query for all users: select * from all_users.select * from dba_users ◆ Oracle view current user connec

  • oracle left join the table can not be found to the left all the data, it is confused 2011-06-09

    oracle left join the table can not be found to the left all the data, it is confused Share: http://topic.csdn.net/u/20080909/17/5bc2ebfa-8fd0-484f-837e-98d75c26d1f8.html select * from a left join b on a.id = b.id and b.name = "" Note that the co

  • oracle delete table recovery. Import and export dmp file oracle locks 2011-05-12

    Recovery of a deleted 1. Do not accidentally delete a database table, restore data from an hour before landing system using the administrator select * from table as of timestamp sysdate-1/12 - two hours before the query data in a table! export table

  • Oracle locks the object and forced to find ways to unlock 2010-06-13

    -- Look for the lock object SELECT /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name, o.owner, o.object_name, o.object_type, s.sid, s.serial# FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id = o.objec