From: hatzinger_m@bmwf1f.bmwf.gv.at
Newsgroups: comp.databases.oracle
Subject: Re: Vanishing data in Dynamic tables v$... ?
Date: 26 Aug 92 10:32:24 GMT
Organization: (Klaus-Michael Hatzinger), BMWF, Vienna

In article <BtJq9x.qt@nntp-sc.Intel.COM>, kortikar@mipos2.intel.com (Aniruddha Kortikar) writes:
> I need some table which has both PID and the tablename. v$access appears to
> be the only table, but it is refreshed every <n> minutes.hence info about
> long held locks will be removed from v$access. hence I can not find out
> which table has locks on it. ALL_OBJECTS will not be useful since I can not 
> join it  on PID.


You don't realy need v$access. You can decode the tables with the
ALL_OBJECT table and the v$lock table (ID1=TABLE#). Try this view....


****************************************************************************
SET PAGESIZE 80

COLUMN table_name FORMAT a17
COLUMN username FORMAT a17
COLUMN pid FORMAT 99999
COLUMN id1 FORMAT 99999

CREATE VIEW user_locks AS
SELECT L.pid PID, S.username, L.TYPE, 
       O.object_name table_name,L.id1,
       DECODE(L.lmode,1,'ROW EXCLUSIVE MODE',
                      2,'ROW SHARE MODE',
                      3,'ROW EXCLUSIVE MODE',
                      4,'SHARE MODE',
                      5,'SHARE/ROW EXCLUSIVE MODE',
                      6,'EXCLUSIVE MODE',NULL) LMODE
  FROM all_objects O,sys.v_$session S,sys.v_$process P,sys.v_$lock L
 WHERE L.id1 = O.object_id(+)
   AND L.id2 = 0
   AND S.paddr=P.addr
   AND P.pid=L.pid
   AND O.object_name IS NOT NULL
/
****************************************************************************


   PID USERNAME          TY TABLE_NAME           ID1 LMODE                      
------ ----------------- -- ----------------- ------ ------------------------   
    26 OPS$HATZINGER_M   TD V_$ACCESS            286 SHARE MODE                 
    27 OPS$HATZINGER_M   TD V_$LOCK              278 SHARE MODE                 
    28 OPS$HATZINGER_M   TD V_$ACCESS            286 SHARE MODE                 
    28 OPS$HATZINGER_M   TM DMDBSTAMM           7124 EXCLUSIVE MODE             
    29 OPS$HATZINGER_M   TD ALL_OBJECTS          168 SHARE MODE                 
    29 OPS$HATZINGER_M   TD USER_LOCKS         10774 SHARE MODE                 
    29 OPS$HATZINGER_M   TD V_$PROCESS           260 SHARE MODE                 
    29 OPS$HATZINGER_M   TD V_$LOCK              278 SHARE MODE                 
    29 OPS$HATZINGER_M   TD V_$SESSION           264 SHARE MODE                 

9 records selected.

================================================================================
                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Klaus-Michael Hatzinger            mail: hatzinger_m@bmwf1f.bmwf.gv.at   |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================
Newsgroups: comp.databases.oracle
From: twod@socrates.demon.co.uk (Ian Parkin)