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

In article <BtAJKF.Axo_at_nntp-sc.Intel.COM>, kortikar_at_mipos2.intel.com () writes:
> I am trying to find out type of locks held by users on various tables.
> I can do this by querying tables such as v$access,v$process etc. but if any
> user hold locks on table for quite long period then that data is removed from
> dynamic performnce tables such as v$access. If he touches the tables again
> then data will appear again.!
>
> How can I get static data from dynamic tables!

I can't believe that ...

v$access doesn't give you any reference wich locks held by users. I think this dynamic table only holds information about the tables used by last access. If you want to get information about the locks you must query the v$lock table for each pid.

You can decode the ID1 column joining it with the OBJECT_ID of the ALL_OBJECT (for special tables like OBJ$ use v$access) table (only for ID2 = 0). So you can get the table name, type and mode of the locks.

modes..............

 WHEN '1' THEN COPY 'ROW EXCLUSIVE MODE' INTO :locks.modetxt;
 WHEN '2' THEN COPY 'ROW SHARE MODE' INTO :locks.modetxt;
 WHEN '3' THEN COPY 'ROW EXCLUSIVE MODE' INTO :locks.modetxt;
 WHEN '4' THEN COPY 'SHARE MODE' INTO :locks.modetxt;
 WHEN '5' THEN COPY 'SHARE/ROW EXCLUSIVE MODE' INTO :locks.modetxt;
 WHEN '6' THEN COPY 'EXCLUSIVE MODE' INTO :locks.modetxt;


================================================================================
                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Klaus-Michael Hatzinger            mail: hatzinger_m_at_bmwf1f.bmwf.gv.at   |
 Bangkasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================