Re: UNDO segments in 9.2

Gene,

First of all, the out of space failure may be perfectly normal. Even if you do a 200M table import there are several other recursive statements that generate a lot of undo information, including but not limited to segment expansion, extent creation, partition operation process, etc. Some culprits could be even a file expansion. To know exactly what is going on use the sql_trace on this session when it runs and then tkprof using sys=yes option to see all the statements and undo information generated by recursive statements.

How do you see the space taken up by the segments? You could do the follwoing query:

SQL> l
  1 select * from seg$
  2 where (file#, block#, ts#)
  3 in
  4 (select file#, block#, ts#
  5* from undo$)
SQL> Look at the blocks column which will tell you the size as of now.

HTH Arup Nanda

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 07, 2003 6:49 PM

> Hi all:
>
> I'm seeing something odd with the undo segments in
> Oracle 9.2. I have a 1G undo tablespace and am
> trying to import a 200M table. This is the only
> transaction going on at the time and it is failing
> because it is running out of space. When I check the
> undo segments I see TWO undo segments of a significant
> size. One that is used by my import and one more,
> which eats up about a half of the tablespace. I
> thought that Oracle would use up the space taken by
> this "other" segment, but it doesn't and my import
> fails. What am I not knowing about the undo segments
> and oracle9.2?
>
> thanks for any help
>
> Gene
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gurelei
> INET: gurelei_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).