Home » RDBMS Server » Server Administration » Trying to re-compile store procedure & process hung
Trying to re-compile store procedure & process hung [message #51647] Tue, 04 June 2002 19:18 Go to next message
Anthony Wong
Messages: 2
Registered: June 2002
Junior Member
Hi,

I am using 9i. The problem that I have is I am trying to re-create an existing stored package using the "create or replace ...." and the process hung & the cause is some users is using the package. Could anyone advice which system table can I find out who is using the stored package.
If I found out I can either kill the session or acknowledge the user to quit.

Report message to a moderator

Re: Trying to re-compile store procedure & process hung [message #51651 is a reply to message #51647] Tue, 04 June 2002 22:14 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Try system views V$LOCKED_OBJECT and V$LOCK. At our system we also have two views: DBA_BLOCKERS and DBA_WAITERS indicating who is blocking who and who is waiting for a lock to be released. These last two don't exist on every environment, so perhaps you'll have to create them yourself.

MHE

Report message to a moderator

Re: Trying to re-compile store procedure & process hung [message #51670 is a reply to message #51647] Wed, 05 June 2002 10:51 Go to previous message
Trifon Anguelov
Messages: 514
Registered: June 2002
Senior Member
Check in v$session table for PREV_SQL_ADDR, PREV_HASH_VALUE, SQL_ADDRESS, SQL_HASH_VALUE columns and in v$sqltext for ADDRESS, HASH_VALUE, PIECE, SQL_TEXT columns.

Create a query and get the session and SQL text being executed in any moment. Then search for the stored procedure name in WHERE clause.

Hope that helps,

clio_usa
OCP - DBA

Visit our web site [url=http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi]Click Here

Report message to a moderator

Previous Topic: Adding dynamic tables...
Next Topic: applying patch?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 16 18:16:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.