Role of system rollback segments in Oracle 10 GB

There is still a rollback segment in Oracle10g named system, which was created by Oracle when the database was created, and the rollback segment exists in the SYSTEM tablespace. It is used to roll back system events. That is to say, the modified image of the data dictionary exists in the system rollback segment.

SQL> select segment_name, owner, tablespace_name, segment_id, file_id from dba_rollback_segs where segment_id = 0;

 

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID

--------------------------------------------------------

System sys system 0 1

We can query the dba_rollback_segs view to obtain the rollback/undo segment information in the system.

To explore the role of system rollback segments, I will do the following experiment:

First, create a test table in a session, and then run a process to insert 1 million pieces of data in batches.

SQL> create table test (data varchar2 (100 ));

 

The table has been created.

 

SQL> begin

2 for v_loop in 1 .. 1000000 loop

3 insert into test values ('test! ');

4 commit;

5 end loop;

6 end;

7/

 

The PL/SQL process is successfully completed.

 

Run the following script in another session.

 

SQL> declare

2 v_cnt int: = 0;

3 begin

4 for v_loop in 1 .. 1000000 loop

5 select count (*) into v_cnt from v $ transaction where xidusn = 0; --- xidusn indicates rollback segment_id

6 if v_cnt> 0 then

7 dbms_output.put_line ('Find it ');

8 end if;

9 end loop;

10 end;

11/

Find it

Find it

Find it

Find it

...... Omitted ...................

This experiment shows that the system rollback segment will still be used by the system, and the v $ transaction View also records the background process.

The system rollback segment is used to record Oracle internal operations, that is, data dictionary changes.

The system rollback segment written in a book will never be used after Oracle is created. In fact, it is wrong.