V$LOCK

[Chandra]

I am seeing 160 row's in my V$LOCK table. These are locks in the database? i startup the database and still i am seeing 160 row's. How do i clear these row's.

Thanks

[gopi]

Okay, I'll take a crack at this:

1. First do some research to what the locks are and why!
2. Next start killing sessions


(these queries are 9i but I'd guess they would work in 8i)
--See if any sessions are waitng due to locks:
SELECT sid, seq#, event, wait_time, state, seconds_in_wait
from v$session_wait a
WHERE event = 'enqueue'

--See the DML (inserts, update, deletes) locks per session
SELECT a.*,b.osuser,b.program
FROM dba_dml_locks a, v$session b
where a.session_id = b.sid

-- Now look at SQL for each session:
SELECT s.sid , s.serial#, s.status, s.osuser , s.username,
t.sql_text, s.machine, s.terminal, p.program
FROM v$session s, v$process p, v$sqltext t
WHERE t.address = s.sql_address
and s.paddr = p.addr
and t.hash_value = s.sql_hash_value
/* and s.sid = 54 */
order by p.spid, t.hash_value, t.piece