Re: dropping Undo Tablespace

Hi,

     Thank you all for the quick response.

Yes, killing the transactions would be the last option and i think i will have to wait until those transactions finish and all first undo tablespace segments offline.

Riyaj - My fast_start_parallel_rollback has been set to LOW (default, i guess). And following is the result for the USED_UBLK and USED_UREC for the respective sessions.

SQL> select d.used_ublk, d.used_urec, a.usn, e.sid from v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e where a.usn=b.usn and a.name=c.segment_name and a.usn=d.xidusn and d.addr=e.taddrand b.status in ('PENDING OFFLINE','UNKNOWN');

 USED_UBLK  USED_UREC        USN        SID
----------               ----------            ----------    ----------
         1                    2                    20        22
       692            60955                   37         16
       386            29993                    4         34

What could be infered from this USED_UBLK and USED_UREC in this case?

Thanks,
Godwin.

On 5/31/07, Ted Coyle <oracle-l_at_webthere.com> wrote:
>
>
>
> The rollback may be huge and it might take longer than waiting for it to
> complete. Just a thought.
>
> Safe is relative. I don't think anyone can answer if it is safe to kill
> long running on your system from the app perspective, but I think it is
> Oracle/technically safe.
>
>
>
> I use the same undo reduction process in production warehouses, but I've
> never done by killing sessions.
>
> . The only option i see here is to kill the session (22,16,34), which will
> offline all the undo segments and then drop the first undo tablespace. This
> is a production box, so just wanted to confirm with you. Is it safe to go
> ahead and kill the session? is there any other work around in this case for
> dropping the first undo tablespace. "
>
> Ted
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.472 / Virus Database: 269.8.4/825 - Release Date: 5/30/2007
> 3:03 PM
>

--
http://www.freelists.org/webpage/oracle-l