Re: dropping Undo Tablespace

Hi,

Riyaj - Following is the output i got after i executed the query,

SQL> select sid, last_et_call, status from v$session where sid in (122,91,42)

SID LAST_CALL_ET STATUS

------ ------------------------------ ------------------
22           208832               ACTIVE
16             384680               ACTIVE
34             385305               ACTIVE

I dont understand the significance of this value. If it indicates how recently the query was active, then the SID has the pending query from about 2 days. Any insight?

Thanks,
Godwin.
On 5/31/07, Shamsudeen, Riyaj <RS2273_at_att.com> wrote:
>
> Godwin
>
> These pending transactions are very small. 1000 block rollback
> shouldn't take too long, shouldn't cause major issues either. Just find out
> if the sessions are active or are they executed DML in the past and those
> changes are uncommitted. V$session.last_call_et and state column should
> help.
>
> If I were you, I would kill these sessions [ if they are not
> active ], as these transactions are pending for couple of hours anyway.
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Godwin vincent
> *Sent:* Thursday, May 31, 2007 3:10 PM
> *To:* Ted Coyle
> *Cc:* oracle-l_at_freelists.org
> *Subject:* 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.taddr and 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