Oracle tables and Solutions

Conclusion 1:OracleLock table and unlock
Select
S. username,
Decode (L. type, 'TT', '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 '20140901 ';
-- The following are related tables:
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. Check the session information of the locked object and the name of the locked object.
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. Locate the session SID of the locked table,
Serial #, OS _user_name, machine name,
Terminal and executed statements
-- More SQL _text and action than above
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. Identify the SID of the locked table,
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 findDatabaseThe locks generated by all DML statements can also be found,
Any DML statement actually produces two locks: one is the table lock and the other is the row lock.
Lock kill command
Alter
System kill session 'sid, serial #'
Select/* + rule */
S. username,
Decode (L. type, 'TT', '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 a lock wait occurs, we may want to know who has locked the table and who has waited.
The following statement can be used to query who has locked the table and who is waiting.
The preceding query result is a tree structure. If a subnode exists, it indicates that a wait occurs.
If you want to know which rollback segment the lock uses, you can also associate it with V $ rollname. xidusn is the USN of the rollback segment.
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

Conclusion 2: a useful search script:

Column sidformat 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 ',

'Redo thread', 'redo thread ',
'Non', 'user name ',
'Tx ',
'Transaction ',
'Tm', 'dml ',
'Ul ', 'pl/SQL user
Lock ',
'Dx ', 'stributed 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, 'clusive ', 'unknown ')
Lockmode,
Decode (request, 0, 'none ',
1,
'Null ',
2, 'row-S ',
3,
'Row-x ',
4, 'share ',
5,
'S/row-x ',
6, 'clusive ', 'unknown') requestmode,
Ctime,
Block B
From v $ lock, all_objects
Where sid> 6
And v $ lock. id1 =
All_objects.object_id;

Locate the SID of a table lock.
Alter system kill session 'sid, serial #';
You can.

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;
Locate the locked object
Then
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
Variable:
 
Table_1,..., table_n: A series of database tables that you want to lock by using the lock TABLE statement.
Lock_mode:
The locking mode you want to set for a database table. You can select one of the following locking modes.
 
& #61510; & #61472; exclusive
& #61510; & #61472; Share row
Exclusive
& #61510; & #61472; Share
 
& #61510; & #61472; share update
& #61510; & #61472; row
Share
& #61510; & #61472; 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
/