Home » SQL & PL/SQL » SQL & PL/SQL » locks
locks [message #19646] |
Tue, 02 April 2002 09:09 |
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 |
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 #19667 is a reply to message #19647] |
Wed, 03 April 2002 05:38 |
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
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:42:57 CDT 2024
|