Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Ganesh.

Asked: September 08, 2001 - 12:39 pm UTC

Answered by: Tom Kyte - Last updated: October 30, 2019 - 4:27 am UTC

Category: Database - Version: 9i

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: How important is "up to date" to you ?

You Asked

Hi Tom,

Waiting to ask u this question.

What is a Undo Tablespace in 9i. Is this similar to Rollback Segments.

What are NonStandard Block sizes Why that non-Standarad.

Why am i not able to create a RS on a Locally Managed Automatically Sized TS.

Till next Time.

Regards,
Ganesh R

and we said...



An undo tablespace is a different way of managing rollback in 9i. Instead of you figuring out you need N rollback segments each of M bytes/extents in size -- you just create one undo tablespace. In this one undo tablespace we'll manage all undo. You specify how long you want undo to be retained (eg: if your longest running query is about 15minutes, you might say "please retain undo for 20minutes before overwriting it"). We'll create, manage, and grow this undo space for you.

So, an undo tablespace is an alternative to a rollback segment.

As for the non-standard block sizes -- that refers to 9i's ability to have more then one block size in a database. When you create the database, SYSTEM will have a block size. That is the "standard" block size for that database. All other block sizes found in that database are "non-standard".

Rollback segments should have equi-sized extents. Automatically sized extents are by definition not equi sized.

and you rated our response

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

To The Point....

September 09, 2001 - 4:20 am UTC

Reviewer: Ganesh from India

Tom... But do u mean oracle is eliminating Rollback segments in 9i.

Why do i need Non Standard Block Sizes.

Hope to get this cleared.



Tom Kyte

Followup  

September 09, 2001 - 8:21 am UTC

An undo tablespace is an alternative -- rollback segments are still present. You will pick whether you want to use an UNDO tablespace (default) or rollback segments.

You might want a non-standard blocksize to support a mixed mode database (mixed OLTP -- they want small blocks with DSS -- they want big blocks).

You would need a non-standard blocksize to transport datafiles from a 4k database to an 8k blocksize database.

You might want a non-standard blocksize to test what the effect of using a 16k blocksize over a 2k blocksize on storage and performance might have without creating an entire new database.

Great Thomas.

September 09, 2001 - 9:15 am UTC

Reviewer: Ganesh from India.

Nothing More to SAY !!!

And Pls Look into the Indian Reprint of u'r book of which we are hearuing rave reviews.

Regards,
Ganesh R

UNDO Tablespace

February 25, 2002 - 10:28 pm UTC

Reviewer: Neeraj Mehta from SINGAPORE

Its useful for beginers to understand the concepts

I am using UNDO, but still getting rollback errors

December 02, 2002 - 5:28 pm UTC

Reviewer: Dean from Nashville

I've checked several times. I am running in AUTO mode. I checked V$UNDOSTAT and undo is being generated. I can even see my UNDO tablespace changing size.

I have a package:

if I do
alter package body compile;
no errors

if I save the source code to a file and try
create or replace package body

I get

create or replace PACKAGE BODY pk_mypkg
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3:
'I:\DB\my\DBFILES\ROLLBACK\my_RBS_SMALL01.DBF'


I have taken all the rollback segments except SYSTEM (and their datafiles even) offline. So I know what the error means, I just don't know why I got it. Sorry, this one has me in a pickle.


Tom Kyte

Followup  

December 02, 2002 - 7:05 pm UTC

what does

select owner, segment_name, segment_type
from dba_segments
where tablespace_name = (select tablespace_name
from dba_data_files
where upper(file_name) like '%MY_RBS_SMALL01.DBF' )

return (and you have start a TAR with support right?)

I will now

December 03, 2002 - 9:53 am UTC

Reviewer: Dean from Nashville

Owner Segment_Name Segment_type
SYS RBS01 ROLLBACK
SYS RBS02 ROLLBACK
SYS RBS03 ROLLBACK
SYS RBS04 ROLLBACK
SYS RBS05 ROLLBACK

FYI the database was upgraded from 8.1.7

Tom Kyte

Followup  

December 03, 2002 - 10:26 am UTC

don't know -- you'll have to work through this one with support -- sorry.

thanks anyway

December 03, 2002 - 10:42 am UTC

Reviewer: Dean from Nashville

I found that there's no problem with shorter source code, but when trying to compile a large package (2500 lines), it bombs.

Do I get a sticker if it's a bug!

Thanks.

December 03, 2002 - 2:23 pm UTC

Reviewer: A reader

Hi Tom,

Will rollback segments be deprecated in favor of Undo Tablespace in future releases of Oracle.

Tom Kyte

Followup  

December 03, 2002 - 2:24 pm UTC

hard to say but if I had to guess.... (in some future release, i could certainly see it happening yes)

Rollback vs UNDOTBS

November 14, 2003 - 3:53 pm UTC

Reviewer: Denise from Virignia, USA

Tom

just got my 9i up and running at work on my 'toy' server.

I tried setting up rollback segments(carryover from 8i)
and got the ora-30019 error(illegal Rollback Operation..blah,blah).

So what I am understanding is that Rollback Segments have
been replaced with a tablespace called UNDOTBS...and in
that tablespace are .dbf files(like regular datafiles)..
that function as rollback segments and are automatically managed by the system if UNDO_Management=AUTO in init.ora file.

So if I needed more undo(rollback)storage I would simply create another .dbf file(like in any other Tablespace)
and don't have to worry about "Snapshot too old" errors any more??..the undo will "automatically" shrink and grow as needed...(man that is so cool!)

am I thinking this correctly?

thanks..

Tom Kyte

Followup  

November 15, 2003 - 8:56 am UTC

read up on it in the admin/concepts guide.

your concepts are correct.

How to drop undo tablespace

March 07, 2004 - 11:40 am UTC

Reviewer: Arun from Patiala India

hi Tom
by mistake size of the undo tablespace given 20G ,we analysed it when space was not left on the system,we tried to reduce the size but it gives error.we have created a new undo tablespace and mentioned in the init ora file. old undo tablespace took offline and deleted undo datafile (physically) but oracle using old undo tablespace.I have tried to remove it from OEM but it shows some segments dependency and status of those segment is null instead of offline(status seen from dba_rollback_segs).
How can i put these segments offline and drop old undo tablespace

Tom Kyte

Followup  

March 07, 2004 - 12:23 pm UTC

ouch -- hope you really didn't do what it sounds like you did.

first, you should have just (assuming 9i AUM here)

a) created new
b) alter system set undo_tablespace=new_one;
c) drop tablespace old_one including datafiles (might have to wait for active transactions to complete)


if you erased the OS files without dropping the tablespace -- well, you could be in a bad way. This is time to call support and get an analysis of where you are and what you've done.

SYSTEM Rollback segment

March 07, 2004 - 3:46 pm UTC

Reviewer: Shankar from Houston, TX

(1) Is there any relation between SYSTEM rollback segment and UNDO_TABLESPACE undo segments containing user transactions?

(2) When the transaction tables on the header of undo segments in UNDO_TABLESPACE are created, does oracle use SYSTEM rollback segment to keep before image values of these changes as well? Also, whenever the transaction table is modified in the users undo segments headers, the before images are also saved in SYSTEM rollback segments?

I was just curious what is the role of SYSTEM rollback segment? Thanks as always. You are the best.


Tom Kyte

Followup  

March 07, 2004 - 4:52 pm UTC

1) no -- other then one is a rollback segment and the other is a tablespace that contains other rollback segments. It'll not use the system rollback segment except for things it must (mostly exceptional cases and system transactions -- like holding undo for in doubt distributed transactions so as to not jam up the regular undo segments)

see
</code> http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=%22system+rollback%22&book=&preference= <code>
for more info on them.

2) the internal system transactions to create an undo tablespace and the segments therein, system rollback. after that, its all about "itself".

overwriting it ??

March 07, 2004 - 4:05 pm UTC

Reviewer: Michal from Frankfurt

I admit that I haven’t read a concept so you can consider it as a waste question.
However I’m little bit worried about "please retain undo for 20minutes before overwriting it". Let’s say that I set a retention period for 20 minutes that should be sufficient for the most of my DMLs.

What happen when I run an unusual, long running DML and it run out of 20 minutes timeout? I suppose that Oracle behaves consistently and raise an error (an equivalent of “ORA-01555: snapshot too old (rollback segment too small)" and roll back my transaction.

I don’t expect that it’ll really overwrite another rollback information within an undo tablespace (preventing different transaction to use an undo information for potential rollback), doesn’t it?

Thanks.


Tom Kyte

Followup  

March 07, 2004 - 4:49 pm UTC

it will overwrite rollback data from COMMITED transactions only. it is "safe".

Possible to specify an Undo Tablespace?

July 04, 2004 - 9:24 pm UTC

Reviewer: Sea from Australia

I am using Oracle 9i and I need to do a 33 hours process to populate 2 tables. My actual preference is not to use any undo tablespace if possible, but it seems like I cannot specifiy NOLOGGING for my update and insert statements. I wonder whether it is possible to specify a particular Undo Tablespace for that process so that it will not affect other production users, and how do you do it?

Another thing is, I have 3 undo tablespaces in my database:
SYS_UNDOTBS, UNDOTBS, and UNDO_LARGE. The SYS_UNDOTBS
is autoextensible but it looks like the size stays at 7,480MB without increasing. However, the UNDOTBS seems to be increasing instead whenever a large process is being executed. How do I find out what is happening with these undo tablespaces and which one is the default undo tablespace? Is it possible to assign different undo tablespaces to different roles/users?

Thanks!

Tom Kyte

Followup  

July 05, 2004 - 9:56 am UTC

you might have 3 undo tablespaces -- but you have only one undo tablespace of any meaning -- the one that is actually being used

show parameter undo

will help you figure out which one that is


the entire purpose of AUM (automatic undo management) is the AUTO part. If you want to pick a specific rbs -- you have to go back to manual undo managment -- but the net effect will be identical -- one rbs will be used by you and everyone else, the other n-1 will be used by everyone else. I don't see this approach buying you anything.


Unless you are building from scratch a 5 terabyte database using inserts/updates -- I'd really be looking at code that takes 33 hours to run to do inserts/updates. Seems less than optimal.



Drop undo tablespace that segments needs recovery

June 06, 2006 - 10:00 am UTC

Reviewer: Sami

Env : 9.2.0.6 , 3 Node RAC,Sun OS 2.9

<Asktom>
a) created new
b) alter system set undo_tablespace=new_one;
c) drop tablespace old_one including datafiles (might have to wait for active 
transactions to complete)
</Asktom>

Tom,

As you mentioned, in step (a) and (b)

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1_NEW


We want to drop this undo tablespace UNDOTBS1 which is not part on any instance.
But when we try to drop the tablespace it is throwing "ORA-01548:
active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace". Is there a way to overcome this?

The datafile for UNDOTBS1 segments got corrupted and in "NEEDS
RECOVERY" state for more than a month. We created new undo tablespace
(UNDOTBS1_NEW)  that time and UNDOTBS1 tablespace not required any
more.

But we are getting an error when we try to do step (c).


Alert_log
=======
Tue Jun  6 13:22:40 2006
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
.
.
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Tue Jun  6 13:22:41 2006
Errors in file /opt/oracle/admin/pwpb4/bdump/pwpb41_smon_26754.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u06/oradata/pwpb4/undo01.dbf'



 SQL>select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU1$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU2$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU3$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU4$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU5$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU6$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU7$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU8$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU9$                      NEEDS RECOVERY   UNDOTBS1
_SYSSMU10$                     NEEDS RECOVERY   UNDOTBS1
_SYSSMU11$                     ONLINE           UNDOTBS2
_SYSSMU12$                     ONLINE           UNDOTBS2
_SYSSMU13$                     ONLINE           UNDOTBS2
_SYSSMU14$                     ONLINE           UNDOTBS2
_SYSSMU15$                     ONLINE           UNDOTBS2
_SYSSMU16$                     ONLINE           UNDOTBS2
_SYSSMU17$                     ONLINE           UNDOTBS2
_SYSSMU18$                     ONLINE           UNDOTBS2
_SYSSMU19$                     ONLINE           UNDOTBS2
_SYSSMU20$                     ONLINE           UNDOTBS2
_SYSSMU21$                     ONLINE           UNDOTBS3
_SYSSMU22$                     ONLINE           UNDOTBS3
_SYSSMU23$                     ONLINE           UNDOTBS3
_SYSSMU24$                     ONLINE           UNDOTBS3
_SYSSMU25$                     ONLINE           UNDOTBS3
_SYSSMU26$                     ONLINE           UNDOTBS3
_SYSSMU27$                     ONLINE           UNDOTBS3
_SYSSMU28$                     ONLINE           UNDOTBS3
_SYSSMU29$                     ONLINE           UNDOTBS3
_SYSSMU30$                     ONLINE           UNDOTBS3
_SYSSMU31$                     ONLINE           UNDOTBS1_NEW
_SYSSMU32$                     ONLINE           UNDOTBS1_NEW
_SYSSMU33$                     ONLINE           UNDOTBS1_NEW
_SYSSMU34$                     ONLINE           UNDOTBS1_NEW
_SYSSMU35$                     ONLINE           UNDOTBS1_NEW
_SYSSMU36$                     ONLINE           UNDOTBS1_NEW
_SYSSMU37$                     ONLINE           UNDOTBS1_NEW
_SYSSMU38$                     ONLINE           UNDOTBS1_NEW
_SYSSMU39$                     ONLINE           UNDOTBS1_NEW
_SYSSMU40$                     ONLINE           UNDOTBS1_NEW

41 rows selected.

SQL> select file_name,TABLESPACE_NAME,status  from dba_data_files
where TABLESPACE_NAME  like 'UNDO%'
 2  ;

FILE_NAME                                TABLESPACE_NAME                STATUS
----------------------------------------
------------------------------ ---------
/u06/oradata/pwpb4/undo01.dbf            UNDOTBS1
 AVAILABLE
/u06/oradata/pwpb4/undo02.dbf            UNDOTBS2
 AVAILABLE
/u06/oradata/pwpb4/undo03.dbf            UNDOTBS3
 AVAILABLE
/u06/oradata/pwpb4/undo01_new.dbf        UNDOTBS1_NEW
 AVAILABLE

SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.

SQL> drop  tablespace undotbs1;
drop  tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping tablespace 

Tom Kyte

Followup  

June 06, 2006 - 11:17 am UTC

sorry - these were "what you should do instead of ERASING FILES WILLY NILLY IN THE FILE SYSTEM"

this is not about getting rid of undo that is in need of recovery - since WE ACTUALLY NEED that undo to make the database "whole" again.

STOP - contact support, do not hit enter, don't touch that keyboard.

rollback to undo

November 03, 2006 - 2:19 am UTC

Reviewer: A reader

My database is running with rollback segments and I want to switch to undo tablespace and I know that how to do this. But in database lot of objects have hard-coded in their statements to use Rollback segments.

[1] Still is the possible for me to switch to UNDO tablespace.

[2] What will happen to those objects which are with hardcoded with to use particular rollback segments. Will they continue to work normally after switching from rollback to undo.

Tom Kyte

Followup  

November 03, 2006 - 9:22 am UTC

yes.

if you are on 9i, you'll set an init.ora parameter:

ops$tkyte%ORA9IR2> set transaction use rollback segment foo;
set transaction use rollback segment foo
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

ops$tkyte%ORA9IR2> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

ops$tkyte%ORA9IR2> alter session set undo_suppress_errors = true;
Session altered.

ops$tkyte%ORA9IR2> set transaction use rollback segment foo;
Transaction set.



In 10g, you have no need, it will just IGNORE it for you:

ops$tkyte%ORA10GR2> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
ops$tkyte%ORA10GR2> set transaction use rollback segment foo;

Transaction set.





 

rollback to undo

November 13, 2006 - 8:54 am UTC

Reviewer: A reader

Apologies Tom,

Not able to understand.

[1] Do developers need to change the objects or
e.g DBA just need to set these parameters in init.ora

set transaction use rollback segment foo
alter session set undo_suppress_errors = true

What is foo in this case ? Is this the name of undotablespace ?

[2] Can I run with UNDO in automatic mode.

[3] What is the signifacance of "set transaction use rollback segment foo" if I am switching to UNDO

Tom Kyte

Followup  

November 14, 2006 - 4:00 am UTC

umm, I showed in 9i - need parameter or alter session.

In 10g - need nothing.


foo was the name of a rollback segment.


2) sure

3) the question was "what happens to applications that use set transaction to use a specific rollback segment when using automatic undo". I was simply showing what happens.

why I still got ORA_1555 using big undo tablespace

January 12, 2007 - 12:39 pm UTC

Reviewer: tony from USA

Hi, Tom:

We have set a undo tablespace in 12G and init.ora like this:

*.undo_management='AUTO'
*.undo_retention=10800
pcsa1.undo_tablespace='UNDOTBS1'

Why i still got error about rollback segment? I thought it's replaced by the undo tablespace.

ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$"

Me Too.

May 12, 2008 - 9:55 am UTC

Reviewer: Richard from London, UK

Re: why I still got ORA_1555 using big undo tablespace (post before this one):

I, too, have seen this with Automatic Undo (10.1.0.3 on RedHat)). Oracle Support's advice was to destroy the existing Undo tablespace & recreate. I started another job before getting the chance to do that, so never found out if it was a true fix, or not.

The issue only ever reared its ugly head when I tried to use Flashback. So, the question has to be: how can Oracle get "confused" like this? Only "oddity" was that the database had been upgraded from 9i.
Tom Kyte

Followup  

May 12, 2008 - 1:48 pm UTC

you do not say if you had undo retention set properly. It does not matter how large your undo tablespace is if the retention is not set properly

Use of undo tablespace while package compilation

September 15, 2008 - 8:27 am UTC

Reviewer: Nilanjan Ray from UK

Hi Tom,

I have this unusual nagging issue. There is a particular package in our system which when we try to compile takes unusually long (at times more that a couple of hours). We have ensured that there is no process accessing the package or its referenced objects. 

v$session wait shows

SQL> ;
  1  SELECT sid
  2  ,EVENT
  3  ,P1TEXT
  4  ,P1
  5  ,P1RAW
  6  ,P2TEXT
  7  ,P2
  8  ,P2RAW
  9  ,P3TEXT
 10  ,P3
 11  ,P3RAW
 12  ,SECONDS_IN_WAIT
 13  ,STATE
 14  FROM v$session_wait
 15* WHERE sid = 15
stowner@STPS2.CHANNEL4.LOCAL> /

       SID EVENT                P1TEXT             P1 P1RAW            P2TEXT             P2 P2RAW         P3TEXT                P3 P3RAW            SECONDS_IN_WAIT STATE
---------- -------------------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------- ---------- ---------------- --------------- ------------
-------
        15 db file sequential   file#               1 0000000000000001 block#          67332 0000000000010704 blocks              1 0000000000000001            5829 WAITED KNOWN
 TIME
           read 

The extent turned out to be one of the undo segments:
SQL> ;
  1   select
  2     owner,
  3     segment_name,
  4     segment_type
  5  from
  6     dba_extents
  7  where
  8     file_id = &P1
  9  and
 10*   &P2 between block_id and block_id + blocks -1
stowner@STPS2.CHANNEL4.LOCAL> /
Enter value for p1: 1
old   8:    file_id = &P1
new   8:    file_id = 1
Enter value for p2: 67332
old  10:   &P2 between block_id and block_id + blocks -1
new  10:   67332 between block_id and block_id + blocks -1

OWNER      SEGMENT_NA SEGMENT_TYPE
---------- ---------- ------------------
SYS        IDL_UB1$   TABLE

We are on 10g:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

We are using locally managed tablespaces.

Any idea what might be causing this?

Many thanks
Nilanjan

Tom Kyte

Followup  

September 16, 2008 - 10:04 pm UTC

the output was not really readable - does that say 5000 plus seconds in a wait for IO?

but that extent is not undo - that is sys.idl_ub1$ - a dictionary table where we store compiled plsql code....

A reader

May 19, 2009 - 3:27 am UTC

Reviewer: A reader

Hi tom,

Does insert statements generated undo? because i had insert into a table and the insert failed due to unable to extend undo tablespace.

Please clear.
Tom Kyte

Followup  

May 23, 2009 - 11:29 am UTC

sure, inserts need to be undone (rolled back)

why do you think they wouldn't?

an insert generates "delete+rowid" at least into undo
an insert into a table with indexes will generate undo for the indexes as well.


ops$tkyte%ORA10GR2> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select used_ublk from v$transaction;

no rows selected

ops$tkyte%ORA10GR2> insert into t select * From all_objects;

49747 rows created.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
        24

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> truncate table t;

Table truncated.

ops$tkyte%ORA10GR2> create index t_idx on t(owner,object_type,object_name);

Index created.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction;

no rows selected

ops$tkyte%ORA10GR2> insert into t select * From all_objects;

49748 rows created.

ops$tkyte%ORA10GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       692





24 undo blocks without indexes, 692 undo blocks with

many many many thanks like always

May 25, 2009 - 4:14 am UTC

Reviewer: A reader


a basic question

July 08, 2009 - 7:17 pm UTC

Reviewer: Rish Gupta from Seattle, WA

Tom,
When I query the v$parameter view
SELECT * FROM V$PARAMETER
WHERE NAME LIKE 'undo%'

I see that that
undo_management =AUTO
undo_tablespace =UNDO_TBS_01

When I run this query
select * from dba_tablespaces
where tablespace_name = 'UNDO_TBS_01'

I see that the segment_space_management is MANUAL.

I'm unable to reconcile the difference between the AUTO undo management and MANUAL segment_space_management.
I thought that whole point of AUM was that one does not need to manage segments, yet the segment_space_management is manual. I'm confused.
Obviously these are 2 different parameters, but I'm unable to grasp the difference. Can you explain?

Thanks in advance.
Rish G.



Tom Kyte

Followup  

July 08, 2009 - 7:49 pm UTC

... I'm unable to reconcile the difference between the AUTO undo management and
MANUAL segment_space_management.
...

why? they have nothing to do with eachother.

auto undo management - we figure out how many rollback segments to create, how big they should be, and try not to overwrite data for undo_retention seconds (it is all about undo)

segment space management is how space is managed in a tablespace - either with freelists/freelist groups (manual) or with bitmap freelists stored in the blocks of individual segements (automatic). It has *nothing* to do with undo.

... I thought that whole point of AUM was that one does not need to manage
segments, yet the segment_space_management is manual. ...

that is so far from true, I'm not sure how you would even arrive at that conclusion. Here is what automatic undo management is about:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1469


and this compares manual versus automatic segment space management
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN10065

Perfect

July 09, 2009 - 1:47 pm UTC

Reviewer: Rish Gupta from Seattle, WA

Tom,
Thank you for your explanation. My confusion arose from wrongly understanding the terms AUTO and MANUAL within their respective contexts. I incorrectly thought that AUTO UNDO MANAGEMENT and AUTO SPACE SEGMENT MANAGEMENT implied the same thing, i.e how space is managed in tablespaces.
Thanks for the lucid explanation.

July 09, 2009 - 5:48 pm UTC

Reviewer: A reader

Hi Sir,
I am using Oracle 9i,manuel undo management,and not using undo tablespace, using old fashion rollback segments.

I am using external tool to monitor oracle and it sometimes raises the below warning:
"Rollback Buffer Wait has increased to a Critical state."

What should I do to stop this warning? Shall I add more rollback segment?

Tom Kyte

Followup  

July 14, 2009 - 4:56 pm UTC

you would think that if a 3rd party tool raised a custom error message like that, they would tell you what to do.

How about you create an undo tablespace, set undo management to automatic, set your undo retention to a value that works well for you.

And then you can forget about it, as we will increase the number of online rollback segments automatically as the load increases to avoid these waits.

UNDO segment

August 03, 2009 - 1:19 am UTC

Reviewer: A reader from India

Hi Tom

what my understanding about UNDO is

whenever a DML or DDL (i am not sure about DDL, please confirm) statement is executed, an undo-statement is generated in UNDO segment corresponding to that statement.

My question is
1. Suppose in a multiueser environment, at a particular time frame, more than one user are using the same undo segment. Suppose some of the users has commited their transactions and some of the users has not yet committed their transactions. Suppose at this moment UNDO buffer, and UNDO file in the disk both becomes full.
My question is, at this moment how oracle decides which entries in the UNDO buffer or in the UNDO file in the disk, will be overwritten.

2.Suppose none of the users has committed their transactions and UNDO buffer and UNDO file in the disk both becomes full. So what happenes, if at this moment a user executes a DML statement. Becauser at this moment there is no space to generate undo statement.

Ani
Tom Kyte

Followup  

August 04, 2009 - 1:44 pm UTC

this is bigger than a bread box as we like to say - meaning - it takes many pages of text to cover this properly. For the full story on this, in my words - I'll have to refer you to Expert Oracle Database Architecture.


In short, in an Undo tablespace - there are many undo segments. Each undo segment consists of a circular ring of extents (which are collections of blocks). The undo in a segment is used in a circular fashion - we move from extent 1 into extent 2, into extent 3, ... and then back into extent 1 - IF AND ONLY IF extent 1 has only committed transactions. If extent 1 has non-committed transactions we cannot reuse it - we have to WAIT. If we would like to move into extent 1 but cannot (because there is an uncommitted transaction) we will attempt to

a) allocate a new extent and stick it in the ring - making that undo segment larger. You say "this cannot be done, disk is full". So then....

b) we look at the other undo segments and see if they have any extra extents they are not using right now. If they do - we steal it from them.

c) if they did not, we look around for any extents we can expire prematurely (extents that have only committed transactions and are needed for read consistency based on your undo retention setting - but are not needed to rollback anything anymore) and expire them. Then we can steal them.


If we do (c), you might get ora-1555's snapshot too old as we will have reused undo before the undo retention period expires.


If a, b and c fail to find space - then (assuming you are not using resumable statements...) we fail the transaction that tried to advance the undo segment into extent 1 (which, if it was the cause of us not being able to advance - IT was the uncommitted transaction in extent 1 - that'll fix the problem for everyone :) )

August 04, 2009 - 9:47 pm UTC

Reviewer: A reader

Respected Sir;

Is there any important difference between automatic undo management and manuel undo management,
other than:

In AUM:
.Rollback segments are created automatically according to size of the undo tbs
.Undo retention parameter.


2-)Is round robin method also used in AUM?
Tom Kyte

Followup  

August 05, 2009 - 8:48 am UTC

those are HUGE differences making manual and automatic semi "incomparable"

Automatic:
rollback segments are created automatically
and un-created automatically
and are sized dynamically in support of your undo retention (will grow based on a timeframe, not just because we have uncommitted transactions)
can steal (move) extents from rollback segment to rollback segment if possible in support of undo retention
can make undo retention a guarantee, not just a "we will try"


Manual:
you pick the number
you pick the size
you lose, none of the above list under automatic is true for manual, none of it


UNDO

October 09, 2009 - 6:49 am UTC

Reviewer: vinod from india

Respected Tom,

want a clarification on undo tablespace.if we change any value in database the old value will be stored in the undo tablespace.how the space is allocated in the undo to store the old value.will it be in blocks if so i have created undo with 8k blocksize and if change a value in database which resides in 16k blocksize how undo accomadates the 16k value ?

rollback and Undo

December 20, 2010 - 8:20 pm UTC

Reviewer: A reader

Hi Tom,

Can you please elaborate about the difference between undo and rollback segments? I thought both stores same undo blocks but in different states (Automatic Undo Mgmt and Manual Undo Mgmt respectively). If yes then what is the disadvantage of using Rollback segments?
Tom Kyte

Followup  

December 21, 2010 - 6:59 am UTC

undo segments are rollback segments are undo segments under the covers.

we call them undo segments when Oracle creates and manages them with automatic undo management.

we call them rollback segments when YOU create and manage them.


but they serve the same purpose - there are things we can do internally to undo segments that you cannot do to rollback segments (like reassign extents) - but under the covers they are pretty much the same concept.

OLTP data

January 17, 2011 - 6:35 am UTC

Reviewer: A reader

Hi,

I have few basic questions:

1) Does Undo block contain any query or statement to update back to the prior stage? or only previous data is stored there?
eg: if
update emp set ename='Scott' where ename='Harry';
is issued then does undo block contain the
update emp set ename='Harry' where ename='Scott'
like statement?

2) If in a OLTP system any continious update is happening then can we retrieve the data on sudden crash if not committed? (DB in NOARCHIVELOG mode)
If yes then will undo block come into play in this case?
Tom Kyte

Followup  

January 23, 2011 - 3:12 pm UTC

1) no, it is binary data that we process in order to figure out what to do.

2) you have to replay the uncommitted transaction, there is no way to reconstruct it - most of it was in memory and not on disk - after a crash, it is quite simply GONE

Query regarding contents of Rodo log,Undo and data block

January 30, 2011 - 3:25 am UTC

Reviewer: A reader

Hi Tom,

I read from somewhere and made some assumptions:-
1) A data block contains a transaction table in the header of it(I'm leaving free,data space of the block out of this discussion). This transaction table holds the undo block# and slot# in which its prior or copy of that data resides before a DML change. Is it right?
2) Undo is also having corresponding status(Active,Committed etc)of that row with its required data in that very slot based on the DML executed. Is it correct?
3) Redo log stores the data block changes,changes in undo block and undo block location details, transaction table for Undo segment. Is it correct? Is the data block changes include the statements executed as well? Is the transaction table of Undo is the same transaction table that undo block holds in discussion #2?
Tom Kyte

Followup  

February 01, 2011 - 4:20 pm UTC

1) more or less. The transaction table points to undo where the transactions are listed, then these transaction tables point to the undo blocks that make up that transaction

2) not really. Just think of undo as either being:

a) part of an active transaction
b) part of a committed transaction

The transaction record is what is marked as "done" or "not done", not all of the undo blocks. It is not managed on a per row basis.

3) redo stores redo. Some of the redo it stores is used for UNDO blocks, some for other blocks. It doesn't directly store transactions tables etc - it just stores "redo" and that redo can be applied to any block - an undo block, a data block, whatever.

Undo segments Count

November 29, 2012 - 10:17 am UTC

Reviewer: A reader

IS there any max limit for undo_segments in undo tablespace ?
10g R2
Tom Kyte

Followup  

November 30, 2012 - 3:52 am UTC

yes - but you need not worry about it, it is automatically managed for you, the automatic internal limit is just that, an internal limit that the database obeys but isn't really exposed to you and me.


January 18, 2013 - 10:26 pm UTC

Reviewer: xiaowei from china

it's no difference between rollback and undo,is it correct?8i version is called rollback and 9i is called undo.
Tom Kyte

Followup  

January 21, 2013 - 3:25 am UTC

rollback segments contain undo.
undo segments contain undo.

rolling back is performed by applying undo (also known as rollback) data.

undo segments are automatically managed by the database in a special undo tablespace.

rollback segments were manually managed by DBAs in permanent tablespaces.

rollback and undo can be considered synonymous for purposes of discussion

Undo tablespace

January 29, 2013 - 10:56 pm UTC

Reviewer: Gopal from Delhi - India

Hello Tom,

In one of my database, I see that my undo tablespace shows 100% used all the time. I see that all the undo segments are in unexpired status and none for active and expired segs. The retention time is set to 60mins. Even, after 60mins of retention time, undo tablespace is not releasing the unexpired segments.

What is causing it to not to release the undo segments as expired or free up space in the undo tablespace?

Tom Kyte

Followup  

January 31, 2013 - 9:49 am UTC

if you had a resource to manage - would you give it up, just to have to reallocate it later?

allocating things is expensive.
deallocating things is expensive.


we have to allocate something at least once - but also at most once.

So, once undo space is allocated - we are going to keep it, there is no point in freeing up the space - it isn't like anything else could use it. there would be no point in it.


What perceived problem are you attempting to solve - a full undo tablespace (like a fully allocated TEMP tablespace) are normal, expected and desirable.

want to delete one of the UNDO tablespace data file.

July 11, 2013 - 1:14 pm UTC

Reviewer: girish from India

Hi Tom,

I want to delete one of the datafile from UNDO tablespace without restarting database. How can I do it.
Tom Kyte

Followup  

July 16, 2013 - 4:00 pm UTC

suggest you create a new undo tablespace and switch over to it and then drop the old including contents and datafiles. You'd have to get the datafile totally empty and you really have no control over that with an undo tablespace

if the datafile has any extents in it - you won't be able to drop it.

undo relation with ORA-1555

September 10, 2013 - 6:22 am UTC

Reviewer: Chakra from India

Hi Tom:

1) Update query started at T time
2) After T1 time, some of its older image in undo has overwritten
3) Now at T2, I issued rollback.

What will happen now? (will it end with ORA-155?)
Please help me on this.


Tom Kyte

Followup  

September 10, 2013 - 10:18 am UTC

#2 is not possible. you cannot overwrite undo for an active transaction - never.


undo can only be reused AFTER a transaction has committed. never ever before.

Offline Undo segments

January 05, 2015 - 1:30 pm UTC

Reviewer: Vignesh from India

When i switch the undo tbs from old to new. I still find that few segments of old undo tbs are unexpired though they are offline. No active transaction using it. It has been 15 days since the switch over but still few segments are unexpired

Rollback and Undo Segment

June 03, 2019 - 11:52 am UTC

Reviewer: Mansi Raval from India

Regarding this answer fro tom
<<<rollback segments contain undo.
undo segments contain undo.

rolling back is performed by applying undo (also known as rollback) data.

undo segments are automatically managed by the database in a special undo tablespace.

rollback segments were manually managed by DBAs in permanent tablespaces.

rollback and undo can be considered synonymous for purposes of discussion >>>>

What if my database parameter shows both this segment available. which one will be used to store the data until we rollback or commit the transaction.
Connor McDonald

Followup  

June 05, 2019 - 12:11 am UTC

"What if my database parameter shows both this segment available"

Not sure what you mean there.

Redo vs undo

October 29, 2019 - 11:05 am UTC

Reviewer: Darek from Poland

I still doesn't understand difference between storage of undo and redo.
1. Undo - it's something we need to undo our uncommited changes in case of rollback transaction, it's correct?
2 Redo it's something we need to redo(execute one more time) operation in case of for example using backup/after failure of database, it's correct?

And here is my question. If ASSM is turn on, where undo data and redo data are stored on physical and logical level of database?

3. Undo data are stored in undo file (undo%.dbf) in undo tablespace?
4. Redo data are stored in the same datafiles in the same tablespace as undo (point 3)?

5. Ealier in disqusion Tom said, that for purpose of this talk redo and undo are synonyms. But they are quite different (see point 1 and 2). Could you explain it?

For bettter understanding it will be nice if you could write some statment, which list undo, redo datafiles wiht their locations.

Thanks in advance

Connor McDonald

Followup  

October 29, 2019 - 11:43 am UTC

Let me do these in reverse

2 Redo it's something we need to redo(execute one more time) operation in case of for example using backup/after failure of database, it's correct?


Yes. Every single change to anything in the database goes into the redo. Think of it like instructions on a map. If I started in San Francisco and now I am in New York, the redo would let me repeat the journey *exactly* including any "backtracks" I made, thus the redo would be:

- start San Fran
- went to las vegas
- went to Phoenix
- whoops that was a mistake, went back to las vegas
- went to denver
- went to kansas
- went to new york

1. Undo - it's something we need to undo our uncommited changes in case of rollback transaction, it's correct?


Yes. And the redo *contains* all the undo as well. Notice the above "journey" - I made a mistake by going to phoenix and I "rolled back" to las vegas. Even when I undo something, the redo has a record of that.

I also need to use undo for *queries*. Search this site for "read consistency".

Redo vs undo

October 29, 2019 - 12:41 pm UTC

Reviewer: Darek from Poland

Thank for you answer.

1. What if the operation "from Las Vegas to Phoenix" wasn't commited? This information will be stored as undo permanently?

a.I thought that undo information are stored only for duration of transaction and eventually retention period of flashback..

b. what's sense of storing uncommited operation history for finished(commited/rollback) transaction?

2. Doe's it mean that undo tablespace contains undo%.dbf files which contains both information (redo data und undo data)?

3. If 2 is correct, wha'ts the format of redo log files (undo%.dbf)? Each operation (record) in this file shouldn't have some flag saying - this operation was commited and this not?

Best regards, Darek

Thanks, for your advice. I'm familar with flashback query :)
Connor McDonald

Followup  

October 30, 2019 - 4:27 am UTC

1. What if the operation "from Las Vegas to Phoenix" wasn't commited? This information will be stored as undo permanently?


Not permanently but we don't throw away stuff just for sake of extra work. It will be flushed out in due course.

b. what's sense of storing uncommited operation history for finished(commited/rollback) transaction?


because an uncommitted transaction might *still* have those changed blocks flushed to disk, so another session still needs to see the undo for that uncommitted information to get a consistent read.

Doe's it mean that undo tablespace contains undo%.dbf files which contains both information (redo data und undo data)?


Other way around. Redo logs contain all changes to the database including any changes to undo blocks in the database.

I'm familar with flashback query :)


Dont forget that *every* query is basically a flashback query