RE: Undo segments in a normal tablespace?

From: Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
Date: Wed, 19 Mar 2014 16:17:54 +0000
Message-ID: <1395245874.11048.45.camel_at_whisk.internal.admin.cam.ac.uk>

You are right - I was fiddling round with the query and the where clause dropped off.

I feel suitably embarrassed!

Thanks

PaulH

On Wed, 2014-03-19 at 16:07 +0000, Jonathan Lewis wrote:
>
> Type 2 is simply the "newer" form of rollback segment - i.e. the thing we're supposed to call an undo segment.
> Your query doesn't show the tablespace_name for the segments - so what makes you think the undo segments are in the same tablespaces as the rest ? It shouldn't be possible.
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Paul Houghton [Paul.Houghton_at_admin.cam.ac.uk]
> Sent: 19 March 2014 16:03
> To: Oracle L
> Subject: Undo segments in a normal tablespace?
>
> We were being slightly silly and running a SQL*Plus script to truncate a
> load of tables and load them (insert into table select ... from
> other_table) in parallel over and over again, starting some new scripts
> before the previous one could complete.
>
> What confused me is that the tablespace was filling up (We got an
> enterpise manager alert). Looking in the tablespace there were a number
> of segments like:
>
> SQL> select segment_name, segment_type, trunc(bytes/1048576) as m from
> dba_segments where bytes> 104857600 order by bytes
>
> ...
> SYS_LOB0001312649C00007$$ LOBSEGMENT 2279
> SYS_LOB0000911233C00007$$ LOBSEGMENT 2311
> _SYSSMU209_2979571356$ TYPE2 UNDO 2346
> XXXXX_TRN_DETAIL TABLE 2357
> SYS_LOB0001158165C00007$$ LOBSEGMENT 2458
> XXXXX_CS_SCTY_TMP TABLE 2636
> _SYSSMU1_1245196837$ TYPE2 UNDO 2699
> XXXXXMM_SCTN_DATA TABLE 2813
> XXXXX_EMAILCOM_INF TABLE 2985
> XXXXXIT_CALC_TBL TABLE 3088
> _SYSSMU39_1215612372$ TYPE2 UNDO 3137
> ...
>
>
> What is TYPE2 UNDO, and how does it end up in a normal tablespace?
>
> Thanks
>
> PaulH
> !��� 0~���+-����?���?���rW�

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Wed Mar 19 2014 - 17:17:54 CET