Re: Vanishing data in Dynamic tables v$... ?

From: <hatzinger_m_at_bmwf1f.bmwf.gv.at>
Date: 26 Aug 92 10:32:24 GMT
Message-ID: <1992Aug26.082425.42_at_bmwf1f.bmwf.gv.at>

In article <BtJq9x.qt_at_nntp-sc.Intel.COM>, kortikar_at_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_at_bmwf1f.bmwf.gv.at   |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================

Received on Wed Aug 26 1992 - 12:32:24 CEST