You might want to create an on logon trigger that enables resumable
transactions and another that catches the transaction & sends you mail
which would be more proactive and informative. The following are
examples that I've used:

create or replace trigger resumable_default
after suspend on database
pragma autonomous_transaction;
dbname varchar2(100);
select initcap(substr(global_name,1,instr(global_name,'.')-1))
into dbname
from global_name;
for a in (select error_msg from dba_resumable

where rtrim(ltrim(error_msg)) is not null) loop
utl_mail.send('','Resumable Transaction error on
'||dbname,'Error is: '||a.error_msg);
end loop;

rem general purpose trigger
create or replace trigger resumable_transaction_trg
after logon on database
pragma autonomous_transaction;
execute immediate 'alter session enable resumable';

Dick Goulet
Senior Oracle DBA
PAREXEL International
information transmitted in this communication is intended only for the
person or entity to which it is addressed and may contain confidential
and/or privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this information
by persons or entities other than the intended recipient is prohibited.
If you received this in error, please destroy any copies, contact the
sender and delete the material from any computer.

From: oracle-l-bounce_at_freelists.org
On Behalf Of Dennis Williams
Sent: Tuesday, December 30, 2008 10:53 AM
To: oracle-l
Subject: Suspended sessions in 10g


We recently upgraded a test database from to in
preparation for the production upgrade tomorrow. People were testing an
application and some reports. Suddenly everyone reported that their
session was "frozen". Some checking in the alert log revealed that there
were many suspended sessions. They reported a datafile couldn't extend.
But that datafile had plenty of space. So we checked the temp tablespace
which was still stored as a data file (not a true tempfile) and
dictionary managed. The report session that had hogged the temp space
was killed and everything quickly returned to normal.

Has anyone seen a spurious message like this?
We have converted the temp tablespace on the test database to a
locally managed tempfile, but there won't be time to replicate the
situation before the production conversion.

Our plan for production is to convert the temp tablespace to a
locally managed tempfile as part of the upgrade. And create a separate
temp tablespace for the report user.

Does anyone have any other suggestions?

Dennis Williams

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 3 | next ›
Discussion Overview
grouporacle-l @
postedDec 30, '08 at 3:53p
activeDec 30, '08 at 9:11p



site design / logo © 2022 Grokbase