Topic: 1z0-082 topic 1 question 64

Which three statements are true about undo segments and the use of undo by transactions in an Oracle database instance? (Choose three.)

A.
An undo segment may be used by multiple transactions simultaneously
B.
Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment
C.
Undo segments have a minimum of three extents
D.
Undo segments can extend when a transaction fills the last extent of the undo segment
E.
A single transaction may use multiple undo segments simultaneously
F.
Undo segments must be stored in a BIGFILE tablespace
G.
Undo segments must be stored in a SMALLFILE tablespace

Re: 1z0-082 topic 1 question 64

F and G are both false!
While the default is smallfile you can also create a bigfile undo tablespace which means both smallfile and bigfile are possible for undo tablespaces

E is also false as each transaction is assigned to only one undo segment, but an undo segment can service more than one transaction at a time -> A is right

Which leaves us with B,C,D as possible answers.

B and D are true -> taken from the Oracle Administrator Workshop Studyguide: Transactions and Undo Data (14 - 5): If an extent fills up and more space is needed, the transaction acquires that space from the next extent in the segment. After all extents have been consumed, the transaction either wraps around back into the first extent or requests a new extent to be allocated to the undo segment (supporting answers B and D)

The right answers for this question are: A,B,D

I would also say that C is false as I am pretty sure that UNDO segments have a minimum of 2 extents (and not 3 extents) to support the circular use of undo segments and this can be achieved by a minimum of 2 extents

Re: 1z0-082 topic 1 question 64

C is correct, in 19c, any simple transaction (e.g. updating only 1 field of 1 record) would generate 3 extents, you can observe the number of extent_id in dba_undo_extents view to justify this.

my test script

set transaction name 'testforanswerc';
update dual set dummy = 'y';

select
   r.name "RBS name",
   t.used_ublk "Undo blocks",
   t.used_urec "Undo recs"
from
   v$session s,
   v$transaction t,
   v$rollname r
where
   t.addr = s.taddr
and
   r.usn = t.xidusn;
   
SELECT SEGMENT_NAME, EXTENT_ID FROM DBA_UNDO_EXTENTS WHERE SEGMENT_NAME='<what you found from above r.name column>'
rollback;

  • ogdru
  • New member
  • Offline

Re: 1z0-082 topic 1 question 64

SQL> create undo tablespace undotbs02;

Tablespace created.

SQL> select distinct tablespace_name, extent_id from dba_undo_extents order by 1,2;

TABLESPACE_NAME                 EXTENT_ID
------------------------------ ----------
UNDOTBS02                               0
UNDOTBS02                               1

Re: 1z0-082 topic 1 question 64

I think it should be A D G

Re: 1z0-082 topic 1 question 64

The answer correct is ADG,
because it is not possible for the undo data generated by one transation to cut across multiple undo segment.When  a transaction does  manage to fill its  undo segment, Oracle will automatically add another extent to the segment so that transaction can continue.

Re: 1z0-082 topic 1 question 64

And it is possible for multiple transations to share one undo segment

Re: 1z0-082 topic 1 question 64

A - true; query SELECT addr,xidusn,start_time FROM v$transaction ORDER BY xidusn; can return multiple ADDR (address of the transaction state object) for a single XIDUSN (undo segment number)
B - true; when an extent (within a segment) is filled up, a transaction either wraps around back into the first extent, or requests a new extent to be allocated
C - false; an undo segment can be formed of a single (one) extent. One extent is the total minimum.   
D - true; see answer B
E - false; when a transaction starts, it is assigned to only one undo segment
F - false; a command CREATE SMALLFILE UNFO TABLESPACE tablespace_name; is valid
G - false; a command CREATE BIGFILE UNFO TABLESPACE tablespace_name; is valid

Sources:
F, G - Oracle Database 19c Documentation: SQL Language Reference > 15 SQL Statements: CREATE SEQUENCE to DROP CLUSTER > CREATE TABLESPACE

Re: 1z0-082 topic 1 question 64

A. An undo segment may be used by multiple transactions simultaneously
Correct - "Multiple active transactions can write concurrently to the same undo segment or to different segments."

B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment
Correct - If the 1st extent is full then the next extent will be used and overwritten if it contains unactive undo.

C. Undo segments have a minimum of three extents
Wrong - No documentation about minimum number of extents.

D. Undo segments can extend when a transaction fills the last extent of the undo segment
Correct, AUTOEXTEND Option with Automatic UNDO management allows extension of UNDO.

E. A single transaction may use multiple undo segments simultaneously
Wrong - Documentation says a transaction may use multiple extents simultaneosly not segments.
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-BB42D7D8-2C93-4F5B-B0C1-6361AEEF4B37

F. Undo segments must be stored in a BIGFILE tablespace
G. Undo segments must be stored in a SMALLFILE tablespace
Wrong - Can be either Bigfile or Smallfile, smallfile is the default.

A B D are correct I think

  • auwia
  • New member
  • Offline

Re: 1z0-082 topic 1 question 64

A. An undo segment may be used by multiple transactions simultaneously --> True
B. Undo segments can wrap around to the first extent when a transaction fills the last extend of the undo segment. --> False, an undo that represents active transactions will not be overwritten by a wrap. Of course it will after the transition finished and undo retention timeout expired.
C. Undo segments have a minimum of three extents. --> False, There is no link about that, probably is false. For people trying to demonstrate by SQL plus, I recommend to simulate a case when only 1 or 2 segment are free for extension... I'm sure oracle will extend the necessary and put data safe.
D. Undo segments can extend when a transaction fills the last extent of the undo segment. --> True
E. A single transaction may use multiple undo segments simultaneously. -->True
F. Undo segments must be stored in a BIGFILE tablespace. --> clearly False
G. Undo segments must be stored in a SMALLFILE tablespace. --> Clearly False

Re: 1z0-082 topic 1 question 64

ABD is correct

Re: 1z0-082 topic 1 question 64

A. An undo segment may be used by multiple transactions simultaneously - True
B. Undo segments can wrap around to the first extent when a transaction fills the last extent of the undo segment - True (undo segment act as a circular buffer)
C. Undo segments have a minimum of three extents - False (each segment has a minimum of two extents)
D. Undo segments can extend when a transaction fills the last extent of the undo segment - True (Undo tablespace are locally managed with auto extent allocation )
E. A single transaction may use multiple undo segments simultaneously - False
F. Undo segments must be stored in a BIGFILE tablespace - False
G. Undo segments must be stored in a SMALLFILE tablespace - False (DBCA automatically creates a smallfile undo tablespece, but it can be configured as bigfile table space as well)
So correct answers are: A, B, D

Re: 1z0-082 topic 1 question 64

A is correct for parallel DML or DDL transaction multiple undo segments can be used simultaneously

Re: 1z0-082 topic 1 question 64

seems to be A, D, G

A because of:
Multiple active transactions can write concurrently to the same undo segment or to different segments. For example, transactions T1 and T2 can both write to undo segment U1, or T1 can write to U1 while T2 writes to undo segment U2.

  • Ekos
  • New member
  • Offline

Re: 1z0-082 topic 1 question 64

i agree

Re: 1z0-082 topic 1 question 64

Correct Answer: CDG
Wrong Answer: ABEF
I could not complete verify C, but in my DB it seems to be the default size on creation, i have not tried to minimize them

UNDO segments are used in a circular fashion.  If there are ten UNDO segments, the first transaction uses the first UNDO segment, the second transaction uses the second UNDO segment, and the eleventh transaction circles back to the first UNDO segment. Once a transaction starts in an UNDO segment, it must finish in that segment.

17 Reply by ama

  • ama
  • New member
  • Offline

Re: 1z0-082 topic 1 question 64

are you really sure that C is correct!!!!???

look below

SQL> select segment_name,count(EXTENT_ID) from dba_undo_extents group by segment_name;
SEGMENT_NAME                   COUNT(EXTENT_ID)
------------------------------ ----------------
_SYSSMU11_3443795589$                        24
_SYSSMU6_1437887270$                         22
_SYSSMU135_1421094332$                        2
_SYSSMU161_3597468454$                        2

Re: 1z0-082 topic 1 question 64

you are right C is wrong
A is the correct one
https://coggle.it/diagram/WuHx64lXs-uDQScg/t/managing-undo-data
https://www.coursehero.com/file/p5uvnu8/10-6-Transactions-and-Undo-Data-Each-transaction-is-assigned-to-only-one-undo/
it's hidden in the course material
So i also go with ADG

Re: 1z0-082 topic 1 question 64

G is wrong
you can ether Changing the Datafiles for an Undo Tablespace to a Fixed Size in 12c

https://docs.oracle.com/database/121/ADMQS/GUID-D42E755D-4B5E-48E5-8D73-8356907DD86C.htm#ADMQS12465