Topic: Database Administration >> locks on database
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Neeraj |
Posted: Oct 04, 2007 08:01:19 AM |
Total Post: 228
Joined: Jan, 2007
|
hi,
i m working on oracle 10g database on ibm aix.
how cud i know how many locks are on my database time to time
thxs |
|
|
 krish |
| Posted: Oct 04, 2007 01:53:40 PM |  |
|
Total Post: 4
Joined: May, 2007
|
I use the below query to find the locks info on Oracle 9i.
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$LOCK, V$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The report generated by this script gives information on sessions
REM which are holding locks and gives the information needed to kill
REM using the ALTER SYSTEM KILL SESSION command.
REM ------------------------------------------------------------------------
REM Main text of script follows:
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
Thanks,
Krishna
|
|
|
|
|
| Mohammed Taj |
| Posted: Oct 04, 2007 01:53:56 PM | |
|
Total Post: 694
Joined: Jul, 2007
|
simpily you can use "oem" for monitoring your database.
but if you want to do it command line then use v$lock, v$enquene_lock,v$locked_object view for know which type of lock exists in database and join it with v$session view to know which session is currently locked.
regards
Mohammed Taj
|
|
|
|
|
| Neeraj |
| Posted: Oct 05, 2007 01:02:07 AM | |
|
Total Post: 228
Joined: Jan, 2007
|
thxs for reply,
can u tell me if my application server might be slowely to respond me. so where shud i c to check wheather my application system is slow & how to resolve it.
thxs
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 242 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
