FAQ
List,

We recently upgraded a test database from 8.1.7.4 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?

Thanks,
Dennis Williams

Search Discussions

  • Goulet, Richard at Dec 30, 2008 at 4:07 pm
    Dennis,


    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
    declare
    pragma autonomous_transaction;
    dbname varchar2(100);
    begin
    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;
    end;


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


    Dick Goulet
    Senior Oracle DBA
    PAREXEL International
    978.313.3426
    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

    List,


    We recently upgraded a test database from 8.1.7.4 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?


    Thanks,
    Dennis Williams
  • Robert Freeman at Dec 30, 2008 at 9:11 pm
    Resumable space allocation (management in 9i I think) ? Is the resumable_timeout parameter set? It is supposed to default to 0.

    RF

    Robert G. Freeman
    Author:
    OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Features (Oracle Press)
    Other various titles out of print now...
    Blog: http://robertgfreeman.blogspot.com
    The LDS Church is looking for DBA's. You do have to be a Church member in
    good standing. A lot of kind people write me, concerned I may be breaking
    the law by saying you have to be a Church member. It's legal I promise! :-)

    From: Dennis Williams
    To: oracle-l
    Sent: Tuesday, December 30, 2008 8:53:14 AM
    Subject: Suspended sessions in 10g

    List,


    We recently upgraded a test database from 8.1.7.4 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?


    Thanks,
    Dennis Williams

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 30, '08 at 3:53p
activeDec 30, '08 at 9:11p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase