Re: HELP: Oracle Data Dictionary

baranoski_at_sis.bms.com () writes:

>Hello,
 

>I'm trying to create a view that shows which user has which table locked, and
>the type of lock.
 

>So far, I can find which type of lock the user has but I haven't been able to
>figure out how to tell which user has which table locked.
 

>Does anyone have nay ideas?
>Any help would be greatly appreciated!

>Thanks,
 

>Lori
>baranoski_at_sis.bms.com

Try it with this script or modify it for your needs. It works for Oracle V7.

create or replace view locks(oname,ouser,lockm,lockt,command) as select substr(b.object_name,1,20),

       c.osuser,
       substr(decode(a.lmode,1,'Null  ',2,'RS    ',3,'RE    ',
                             4,'S     ',5,'RSE   ',6,'E     ',a.lmode),1,6),
       a.type,
       substr(decode(c.command,
              1,'create table',
              2,'insert',
              3,'select',
              4,'create cluster',
              5,'alter cluster',
              6,'update',
              7,'delete',
              8,'drop',
              9,'create index',
             10,'drop index',
             11,'alter index',
             12,'drop table',
             15,'alter table',
             17,'grant',
             18,'revoke',
             19,'create syn',
             20,'drop syn',
             21,'create view',
             22,'drop view',
             26,'lock table',
             27,'no operation',
             28,'rename',
             29,'comment',
             30,'audit',
             31,'noaudit',
             39,'create tablespace',
             40,'alter tablespace',
             41,'drop tablespace',
             42,'alter session',
             43,'alter user',
             44,'commit',
             45,'rollback',
             46,'savepoint',
              c.command),1,20)
from v$lock      a,
     dba_objects b,
     v$session   c
where a.id1=b.object_id
      and
      a.sid=c.sid
      and 
      a.sid>6

Good luck! Ekkehard

-- 
| Ekkehard Schulz, Alcatel SEL AG Berlin, Department VB/EOB     |
| Colditzstrasse 34-36, D 12099 Berlin, Germany                 |
| +49-30-70 02 47 71 (telephone) | +49-30-70 02 26 67 (telefax) |
| schulz_e_at_slbh01 (unix-alcanet) | oracle_at_slbh01 (unix-alcanet) |