Re: Oracle locks

"Andrew Allen" <andrew.allen_at_handleman.com> wrote in message news:3E5BE285.5010403_at_handleman.com...
> DA Morgan wrote:
> > Stefano wrote:
> >
> >
> >>Hi,
> >>
> >>I need to keep track of when a lock is created and destroyed on a
> >>specific table, I know that V$LOCK (and other views) contains the
> >>information I need, but I need to create a sort of audit trail. I
> >>tried with the following:
> >>
> >>audit insert on V$LOCK
> >>audit delete on V$LOCK
> >>
> >>and I verified that when a lock is created one or more rows are
> >>created (and can be seen) in V$LOCK, but nevertheless the addition of
> >>new rows is not recorder in the audit trail table and infact:
>
> v$ views are virtual views. They DO NOT exist. The are presented by
> the engine so that we mortals can see what is going on inside.
> Besides V$LOCK is a public synonym. The real virtual table is V_$LOCK.
>
> >>
> >>select USERNAME, TERMINAL,
> >>to_char(TIMESTAMP,'dd-mon-yyyy hh:mm:ss'),
> >>OBJ_NAME, ACTION_NAME
> >>from dba_audit_object
> >>
> >>returns no new rows.
> >>I guess that this is because rows are added somewhere else and since
> >>V$LOCK is a view the events of INSERT and DELETE are not related to
> >>the view itself but to the tables underneath.
> >>I tried to retrieve the information about which tables lay under
> >>V$LOCK view but I wasn't able.
> >>Is there somebody that is willing to provide me this piece of
> >>information?
> >>Is there somebody that knows a different solution to my problem

Well, well, well here we are.
Catch them :)
View: v$_lock1

select addr,ksqlkadr,ksqlkses,ksqlkres,

       ksqlkmod,ksqlkreq

from x$kdnssc

where bitand(kssobflg,1)!=0

and (ksqlkmod!=0 or ksqlkreq!=0)

union all

select addr,ksqlkadr,ksqlkses,ksqlkres,

       ksqlkmod,ksqlkreq

from x$kdnssf

where bitand(kssobflg,1)!=0

and (ksqlkmod!=0 or ksqlkreq!=0)

union all

select addr,ksqlkadr,ksqlkses,ksqlkres,

       ksqlkmod,ksqlkreq

from x$ksqeq

where bitand(kssobflg,1)!=0

and (ksqlkmod!=0 or ksqlkreq!=0)

/

View: v$_lock

select laddr,kaddr,saddr,raddr,lmode,

       request

from v$_lock1

union all

select addr,ksqlkadr,ksqlkses,ksqlkres,

       ksqlkmod,ksqlkreq

from x$ktadm

where bitand(kssobflg,1)!=0

and (ksqlkmod!=0 or ksqlkreq!=0)

union all

select addr,ksqlkadr,ksqlkses,ksqlkres,

       ksqlkmod,ksqlkreq

from x$ktcxb

where bitand(ksspaflg,1)!=0

and (ksqlkmod!=0 or ksqlkreq!=0)

/

View: v$lock

select l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,

       r.ksqrsid1,r.ksqrsid2,l.lmode,

       l.request

from v$_lock l,x$ksuse s,x$ksqrs r

where l.saddr=s.addr

and l.raddr=r.addr

/

Are you happy body? :)

OK.
And what now?
I hope you'll understand that any audit those tables is a bad idea!

Best regards, Sergey. Received on Wed Feb 26 2003 - 00:30:57 CST