Oracle Concepts - Oracle undo tips
Oracle Tips by Burleson Consulting
Administering Oracle UNDO
Oracle allows you to make changes to the database in
kind of a “try it before you buy it mode”. What this means is that the changes
you make in the database can only be seen by you, at first. Hence, you can make
changes, check those changes and make sure they are ok, before you let anyone
else see them. Once you are sure you like your changes, you issue the commit
command, and everyone else can then see them.
This type of functionality gives us database
consistency. This means that the data in the database, when you query it, is
always consistent to the same point in time. For example, if you query two
tables at the same time, say tables named PARENT and CHILD, you will see record
from both tables as they looked at the same point in time. This means that no
one can slip a record into the CHILD table, while you are reading the PARENT
table, or vice versa. The assurance that the records you will read will be
consistent to the same point in time, is known as read consistency. Read
consistency gives your query’s results as they were the moment you pressed
Oracle Undo segments support these read consistent
operations (and other database operations such as flashback operations which we
will discuss later in this book). Oracle creates an undo segment in the SYSTEM
tablespace when the database is created. This undo segment only supports
operations in the SYSTEM tablespace. Hence, if you are going to use other
tablespaces (and you should!), then you will need other undo segments.
"# of Records",
from v$locked_object a,
see code depot for full script
a.object_id = b.object_id
a.xidusn = c.segment_id
a.xidusn = d.xidusn
a.xidslot = d.xidslot
d.addr = e.taddr;
So, where do we get these other undo segments? We create
an undo tablespace with the create undo tablespace command (we will talk about
tablespaces in more detail next) and Oracle will create the undo
segments and manage the for us automatically.
When an Oracle Database is first created, an undo
tablespace can be created at that time, and we recommend this approach. Before
you can use the undo tablespace, we need to set a few parameters (see, there are
those parameters again, they show up everywhere!)
You can use the alter system command to change any of
the parameters listed above at any time.
However, please note that if you change the Undo
Tablespace by changing the undo_tablespace parameter, there still may be active
transactions against the old tablespace. Keep that in mind before you drop it,
it could result in one of the DBA’s most common errors: ORA-01555, snapshot too
old, rollback segment too small!
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.