Home » SQL & PL/SQL » SQL & PL/SQL » locks
locks [message #19646] Tue, 02 April 2002 09:09 Go to next message
shafik
Messages: 13
Registered: March 2002
Junior Member
Yaar how to unlock tables in a database.
I know the syntax for locking which is
SQL > lock table
in exclusive mode;

But howe to unlock the table.

Anyone ?
Re: locks [message #19647 is a reply to message #19646] Tue, 02 April 2002 09:33 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
commit unlocks all locks.

SQL> lock table dept in exclusive mode;

Table(s) Locked.

SQL> @show_locks
Username (LIKE format - default= all):

Locks by users (except type MR) by

USER OS PID SID USER ORA VLOCK TYPE MODE W

-------- ---- ------ -------- --------------------- --------------------------- ---- -

oracle 4 22269 REDO-LOG REDO LOG X

6 22273 TEMPORARY SEGMENT TEMPORARY SEGMENT RX

12 18462 SCOTT ROW LOCK DML SCOTT.DEPT X

SQL> commit;
SQL> @show_locks
Username (LIKE format - default= all):

Locks by users (except type MR) by

USER OS PID SID USER ORA VLOCK TYPE MODE W

-------- ---- ------ -------- --------------------- --------------------------- ---- -

oracle 4 22269 REDO-LOG REDO LOG X

6 22273 TEMPORARY SEGMENT TEMPORARY SEGMENT RX

SQL> spool off
Re: Does ROLLBACK to removes all locks [message #19652 is a reply to message #19647] Tue, 02 April 2002 21:55 Go to previous messageGo to next message
Skumar
Messages: 17
Registered: April 2002
Junior Member
Does ROLLBACK to removes all locks
How does one what are the tables locked.
Re: Does ROLLBACK to removes all locks [message #19667 is a reply to message #19647] Wed, 03 April 2002 05:38 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
Yes. Commit or rollback ends the transaction and releases all locks. I use this script to show locks. You need access to dba views.

REM =====================================================================
REM Procedure : lockv7
REM Author : Herve Delbarre
REM Subject : List of locks by users(except MR)
REM Oracle db : V7.
REM =====================================================================
SET ECHO OFF
SET TERM ON
SET TIMING OFF
SET HEAD ON
SET VERI OFF
SET FEED OFF
SET PAUSE OFF
SET PAGES 66
SET RECSEP OFF
SET LINES 132
SET ARRAYSIZE 5

BTITLE OFF
TTITLE OFF

CLEAR BREAKS
CLEAR COMPUTE
CLEAR COLUMNS
CLEAR SCREEN

COL l FOR A78 TRUNC

ACCEPT us_ CHAR PROMPT "Username (LIKE format - default= all): "

TTITLE CENTER "Locks by users (except type MR) by &&us_" SKIP -
RIGHT ""

COL username FOR A08 HEAD "USER OS" TRUNC
COL pid FOR 999 HEAD "PID" TRUNC
COL spid FOR A06 HEAD "SID" TRUNC
COL ora FOR A08 HEAD "USER ORA" TRUNC
COL lock FOR A10 HEAD "LOCKS" TRUNC
COL type FOR A27 HEAD "TYPE" WRAPPED
COL lmode FOR A04 HEAD "MODE" TRUNC
COL wait FOR A01 HEAD "W" TRUNC

BREAK ON username -
ON pid -
ON spid -
ON ora -
ON lock

-- SPOOL lockv7

SELECT p.username ,
p.pid ,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
AND p.username LIKE NVL('&&us_','%')
ORDER BY
1,
2,
3,
4,
5
/
-- SPOOL OFF
Previous Topic: column naming conversion
Next Topic: about indexes
Goto Forum:
  


Current Time: Thu Mar 28 09:44:50 CDT 2024