Home » SQL & PL/SQL » SQL & PL/SQL » oracle resource not being released -2
oracle resource not being released -2 [message #36568] Tue, 04 December 2001 23:35 Go to next message
dennis
Messages: 31
Registered: June 1998
Member
That when we were running some procedure
having few insert and update statement, during the processing if we terminate the same, here the
oracle is not terminating the session as said in oracle not being released -1, but the problem is it is still processing the procedure and updating the tables as given in the procedure.
thanks and regards
Dennis

----------------------------------------------------------------------
Re: oracle resource not being released -2 [message #36569 is a reply to message #36568] Tue, 04 December 2001 23:56 Go to previous message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Are you sure that the process is not simply rolling back the transactions made? Does this procedure have a heavy transaction load?

Running this:

SELECT SUBSTR(USERNAME,1,10) username,
SID,
SUBSTR( OSUSER,1,6) OSUSER,
XIDUSN,
USED_UBLK,
USED_UBLK*V$PARAMETER.VALUE/1024 KBYTES,
ROUND(USED_UBLK*V$PARAMETER.VALUE/1024/1024, 2) MBYTES,
LOG_IO,
PHY_IO
FROM V$TRANSACTION, V$SESSION, V$PARAMETER
WHERE V$SESSION.SADDR=SES_ADDR
AND V$PARAMETER.NAME='db_block_size'

Will tell you the state of the rollback segments in use by particular sessions. Run it over and over again and it will tell you if something is processing inserts/updates (rollback is increasing) or is rolling back (rollback is decreasing)

Rob

----------------------------------------------------------------------
Previous Topic: stored procedure
Next Topic: Pass default values to procedure
Goto Forum:
  


Current Time: Fri Mar 29 10:08:22 CDT 2024