Re: update & rollback segment mechanism

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 24 Oct 2003 12:46:37 +0100
Message-ID: <3f99111e$0$9465$ed9e5944@reading.news.pipex.net>

This is a little difficult to follow without working through yourself.

First start a sqlplus session - session 1

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 12:26:21 2003

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> create table t2(id number,description varchar2(10));

Table created.

SQL> insert into t2 values(1,'Record 1');

1 row created.

SQL> insert into t2 values(2,'Record 2');

1 row created.

SQL> commit;

Commit complete.

Here is my table with just 2 records in it. These should be in the same block.

SQL> update t2 set description='Update 1' where id=1;

1 row updated.

OK now create a second session to look at what this transaction is doing -- thanks to Rene Nyffenegger for this query.

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 12:27:06 2003

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL>select
2 s.sid, r.name
3 from

  4      v$session s,
  5      v$transaction t,
  6      v$rollname r
  7    where
  8      t.addr = s.taddr and
  9*     r.usn  = t.xidusn;

       SID NAME
---------- ------------------------------
        11 _SYSSMU9$

There we are my transaction has appropriated the (9i system managed) undo segment named _SYSSMU9$ for its undo record.

In a 3rd Session we now update the other record.

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 24 12:36:02 2003

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> update t2 set id=3 where id=2;

1 row updated.

SQL> switch back to the second session and reissue the same query

SQL> ;
  1 select
  2 s.sid, r.name
  3 from

  4      v$session s,
  5      v$transaction t,
  6      v$rollname r
  7    where
  8      t.addr = s.taddr and
  9*     r.usn  = t.xidusn

SQL> / SID NAME

---------- ------------------------------
        16 _SYSSMU3$
        11 _SYSSMU9$

So now we have two transactions both using different undo segments.

As to where the rollback segment is located, it is just a database segment so it goes into the buffer pool along with all the other database blocks. Oracle would be unusable if rollback data were to be stored only on disk.

corrections and clarifications wlecome.

-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk
"utkanbir" <hopehope_123_at_yahoo.com> wrote in message
news:f6c90ebe.0310240309.171545_at_posting.google.com...


> Hi Gurus,

>

> I have a question about update mechanism. How does oracle manages the

> buffers  when multiple rows are updated by different transactions? For

> instance, imagine there is a database block which has recordA and

> recordB and the data is cached in shared memory in block1. When

> recordA is updated ,  the original block is copied into the rollback

> segment.Then when recordB is also updated without commiting the first

> transaction, does oracle creates a new block in rollback segment(or

> undo tablespace) or use the existing one in order to keep the original

> version of recordB?

>

> Also , since the rollback segment is on disk , is there any buffer in

> shared memory which rollback data is cached ?

>

> Kind Regards,

> hope

Received on Fri Oct 24 2003 - 06:46:37 CDT