RE: Undo segments in a normal tablespace?
From: Stephen Miller <miller_stephen_at_usa.net>
Date: Wed, 19 Mar 2014 18:06:52 -0400
Message-ID: <574scswF14768S02.1395266812_at_web02.cms.usa.net>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 19 2014 - 23:06:52 CET
Date: Wed, 19 Mar 2014 18:06:52 -0400
Message-ID: <574scswF14768S02.1395266812_at_web02.cms.usa.net>
Gotta love that BASE64. Any idea why some posts come though that way?
And for those that only got BASE64, here is the real message:
----------------------------------------------------------------------------
You are right - I was fiddling round with the query and the where clause
dropped off.
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
> _at_jloracle
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce@freelists.org] on behalf of Paul Houghton [Paul.Houghton@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
>
> 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@freelists.org] on behalf of Paul Houghton [Paul.Houghton@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
Stephen Miller - email: miller_stephen at usa.net
------ Original Message ------
Received: 12:18 PM EDT, 03/19/2014
From: Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
To: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Cc: Oracle L <oracle-l_at_freelists.org>
Subject: RE: Undo segments in a normal tablespace?
Received: 12:18 PM EDT, 03/19/2014
From: Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
To: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Cc: Oracle L <oracle-l_at_freelists.org>
Subject: RE: Undo segments in a normal tablespace?
WW91IGFyZSByaWdodCAtIEkgd2FzIGZpZGRsaW5nIHJvdW5kIHdpdGggdGhlIHF1ZXJ5IGFuZCB0
aGUgd2hlcmUgY2xhdXNlDQpkcm9wcGVkIG9mZi4NCg0KSSBmZWVsIHN1aXRhYmx5IGVtYmFycmFz
c2VkIQ0KDQpUaGFua3MNCg0KUGF1bEgNCg0KT24gV2VkLCAyMDE0LTAzLTE5IGF0IDE2OjA3ICsw
MDAwLCBKb25hdGhhbiBMZXdpcyB3cm90ZToNCj4gDQo+IFR5cGUgMiBpcyBzaW1wbHkgdGhlICJu
ZXdlciIgZm9ybSBvZiByb2xsYmFjayBzZWdtZW50IC0gaS5lLiB0aGUgdGhpbmcgd2UncmUgc3Vw
cG9zZWQgdG8gY2FsbCBhbiB1bmRvIHNlZ21lbnQuDQo+IFlvdXIgcXVlcnkgZG9lc24ndCBzaG93
IHRoZSB0YWJsZXNwYWNlX25hbWUgZm9yIHRoZSBzZWdtZW50cyAtIHNvIHdoYXQgbWFrZXMgeW91
IHRoaW5rIHRoZSB1bmRvIHNlZ21lbnRzIGFyZSBpbiB0aGUgc2FtZSB0YWJsZXNwYWNlcyBhcyB0
aGUgcmVzdCA/IEl0IHNob3VsZG4ndCBiZSBwb3NzaWJsZS4NCj4gDQo+IA0KPiBSZWdhcmRzDQo+
IEpvbmF0aGFuIExld2lzDQo+IGh0dHA6Ly9qb25hdGhhbmxld2lzLndvcmRwcmVzcy5jb20NCj4g
QGpsb3JhY2xlDQo+IA0KPiBfX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19f
DQo+IEZyb206IG9yYWNsZS1sLWJvdW5jZUBmcmVlbGlzdHMub3JnIFtvcmFjbGUtbC1ib3VuY2VA
ZnJlZWxpc3RzLm9yZ10gb24gYmVoYWxmIG9mIFBhdWwgSG91Z2h0b24gW1BhdWwuSG91Z2h0b25A
YWRtaW4uY2FtLmFjLnVrXQ0KPiBTZW50OiAxOSBNYXJjaCAyMDE0IDE2OjAzDQo+IFRvOiBPcmFj
bGUgTA0KPiBTdWJqZWN0OiBVbmRvIHNlZ21lbnRzIGluIGEgbm9ybWFsIHRhYmxlc3BhY2U/DQo+
IA0KPiBXZSB3ZXJlIGJlaW5nIHNsaWdodGx5IHNpbGx5IGFuZCBydW5uaW5nIGEgU1FMKlBsdXMg
c2NyaXB0IHRvIHRydW5jYXRlIGENCj4gbG9hZCBvZiB0YWJsZXMgYW5kIGxvYWQgdGhlbSAoaW5z
ZXJ0IGludG8gdGFibGUgc2VsZWN0IC4uLiBmcm9tDQo+IG90aGVyX3RhYmxlKSBpbiBwYXJhbGxl
bCBvdmVyIGFuZCBvdmVyIGFnYWluLCBzdGFydGluZyBzb21lIG5ldyBzY3JpcHRzDQo+IGJlZm9y
ZSB0aGUgcHJldmlvdXMgb25lIGNvdWxkIGNvbXBsZXRlLg0KPiANCj4gV2hhdCBjb25mdXNlZCBt
ZSBpcyB0aGF0IHRoZSB0YWJsZXNwYWNlIHdhcyBmaWxsaW5nIHVwIChXZSBnb3QgYW4NCj4gZW50
ZXJwaXNlIG1hbmFnZXIgYWxlcnQpLiBMb29raW5nIGluIHRoZSB0YWJsZXNwYWNlIHRoZXJlIHdl
cmUgYSBudW1iZXINCj4gb2Ygc2VnbWVudHMgbGlrZToNCj4gDQo+IFNRTD4gIHNlbGVjdCBzZWdt
ZW50X25hbWUsIHNlZ21lbnRfdHlwZSwgdHJ1bmMoYnl0ZXMvMTA0ODU3NikgYXMgbSBmcm9tDQo+
IGRiYV9zZWdtZW50cyB3aGVyZSBieXRlcz4gMTA0ODU3NjAwIG9yZGVyIGJ5IGJ5dGVzDQo+IA0K
PiAgIC4uLg0KPiAgIFNZU19MT0IwMDAxMzEyNjQ5QzAwMDA3JCQgICAgICAgICAgICAgIExPQlNF
R01FTlQgICAgICAgICAgICAgICAyMjc5DQo+ICAgU1lTX0xPQjAwMDA5MTEyMzNDMDAwMDckJCAg
ICAgICAgICAgICAgTE9CU0VHTUVOVCAgICAgICAgICAgICAgIDIzMTENCj4gICBfU1lTU01VMjA5
XzI5Nzk1NzEzNTYkICAgICAgICAgICAgICAgICBUWVBFMiBVTkRPICAgICAgICAgICAgICAgMjM0
Ng0KPiAgIFhYWFhYX1RSTl9ERVRBSUwgICAgICAgICAgICAgICAgICAgICAgIFRBQkxFICAgICAg
ICAgICAgICAgICAgICAyMzU3DQo+ICAgU1lTX0xPQjAwMDExNTgxNjVDMDAwMDckJCAgICAgICAg
ICAgICAgTE9CU0VHTUVOVCAgICAgICAgICAgICAgIDI0NTgNCj4gICBYWFhYWF9DU19TQ1RZX1RN
UCAgICAgICAgICAgICAgICAgICAgICBUQUJMRSAgICAgICAgICAgICAgICAgICAgMjYzNg0KPiAg
IF9TWVNTTVUxXzEyNDUxOTY4MzckICAgICAgICAgICAgICAgICAgIFRZUEUyIFVORE8gICAgICAg
ICAgICAgICAyNjk5DQo+ICAgWFhYWFhNTV9TQ1ROX0RBVEEgICAgICAgICAgICAgICAgICAgICAg
VEFCTEUgICAgICAgICAgICAgICAgICAgIDI4MTMNCj4gICBYWFhYWF9FTUFJTENPTV9JTkYgICAg
ICAgICAgICAgICAgICAgICBUQUJMRSAgICAgICAgICAgICAgICAgICAgMjk4NQ0KPiAgIFhYWFhY
SVRfQ0FMQ19UQkwgICAgICAgICAgICAgICAgICAgICAgIFRBQkxFICAgICAgICAgICAgICAgICAg
ICAzMDg4DQo+ICAgX1NZU1NNVTM5XzEyMTU2MTIzNzIkICAgICAgICAgICAgICAgICAgVFlQRTIg
VU5ETyAgICAgICAgICAgICAgIDMxMzcNCj4gICAuLi4NCj4gDQo+IA0KPiBXaGF0IGlzIFRZUEUy
IFVORE8sIGFuZCBob3cgZG9lcyBpdCBlbmQgdXAgaW4gYSBub3JtYWwgdGFibGVzcGFjZT8NCj4g
DQo+IFRoYW5rcw0KPiANCj4gUGF1bEgNCj4gIe+/ve+/vX/vv70gMH7vv73vv73vv70rLe+/ve+/
ve+/ve+/vT/vv73vv73vv70/77+977+977+9clfvv70NCg0K
--
http://www.freelists.org/webpage/oracle-l