RE: Suspended sessions in 10g
Date: Tue, 30 Dec 2008 11:07:51 -0500
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
select initcap(substr(global_name,1,instr(global_name,'.')-1)) into dbname
for a in (select error_msg from dba_resumable
where rtrim(ltrim(error_msg)) is not null) loop
utl_mail.send('<mail Address>','Resumable Transaction error on
'||dbname,'Error is: '||a.error_msg);
rem general purpose trigger
create or replace trigger resumable_transaction_trg after logon on database
execute immediate 'alter session enable resumable'; end;
Senior Oracle DBA
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.
[mailto: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 22.214.171.124 to 10.2.0.4 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?