RE: Undo segments in a normal tablespace?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 19 Mar 2014 16:07:25 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE3C0E_at_exmbx05.thus.corp>


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
_at_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� i0zX+n{+i^ Received on Wed Mar 19 2014 - 17:07:25 CET

Original text of this message