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� †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Wed Mar 19 2014 - 17:07:25 CET