I think it will be much easier to check the v$access view and find who
is accessing the object ...
<quote>
This view displays objects in the database that are currently locked
and the sessions that are accessing them.
Column Datatype Description =20
SID=20
NUMBER=20
Session number that is accessing an object=20
=20
OWNER=20
VARCHAR2(64)=20
Owner of the object=20
=20
OBJECT=20
VARCHAR2(1000)=20
Name of the object=20
=20
TYPE=20
VARCHAR2(24)=20
Type identifier for the object=20
---------------------------------------------------------------------------=
Support and Historical Notes for "V$ACCESS"
View Definition:
Use the following SQL to see the view definition of the related GV$ vie=
w:
SELECT view_definition FROM v$fixed_view_definition
WHERE view_name=3D'GV$ACCESS';
Useful Join Columns:
SID - Join to <View:V$SESSION> . SID
</quote>
HTH
Dimitre
On 6/9/05, Muqthar Ahmed <Muqthar.Ahmed_at_decoratetoday.com> wrote:
> Thanks Egor....I am reading Metalink Note:215858.1
>=20
> Muqthar
>=20
> -----Original Message-----
> From: Egor Starostin [mailto:egorst_at_gmail.com]
> Sent: Thursday, June 09, 2005 1:19 PM
> To: Muqthar.Ahmed_at_decoratetoday.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: oradebug dump processstate 10
>=20
>=20
> > I have a function called GET_UTL_DIR and currently it is locked in the =
database. When I try to re-compile it I get the following error:
> > ORA-04021: timeout occurred while waiting to lock object GET_UTL_DIR
> >
> > I have verified in v$lock but no user is locking.
> Such locks (when someone is executing function and you are trying to
> recompile it) are not displayed in v$lock.
> > Therefore, I have used oradebug command to get more info:
> I'd say that 'oradebug dump processstate 10'' is some kind of overkill
> in such situation.
> You can easily find locker (not only for your case but for every
> locking situation) issuing either "oradebug hanganalyze 2" or just
> "alter session set events 'immediate trace name hanganalyze level
> 2';". You will quickly get easily readable and small trace file with
> tree(s) of blockers/waiters.
>=20
> Read Metalink Note:215858.1 (Interpreting HANGANALYZE trace files to
> diagnose hanging and performance problems) for further info about
> hanganalyze event.
>=20
> --
> Egor
> http://www.oracledba.ru/orasrp/
> Free Oracle Session Resource Profiler
>=20
> * * * * * * * * *
>=20
> The information contained in this E-mail message is privileged, confident=
ial, and may be protected from disclosure; please be aware that any other u=
se, printing, copying, disclosure or dissemination
> of this communication may be subject to legal restriction or sanction. If=
you think that you have received this E-mail message in error, please repl=
y to the sender and delete it from your computer. T
> hank you.
> --
> http://www.freelists.org/webpage/oracle-l
>
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 09 2005 - 14:09:44 CDT