session1

SQL> select to_number(substrb(dbms_session.unique_session_id, 1, 4), 'xxxx') sid,

  2         to_number(substrb(dbms_session.unique_session_id, 5, 4), 'xxxx') audsid

  3    from dual;

       SID     AUDSID

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

        29        923

SQL> 

SQL> delete from t1;

delete from t1

*

ERROR at line 1:

ORA-00028: your session has been killed

ORA-00028: your session has been killed

session2

SQL> alter system kill session '29,923' immediate;

alter system kill session '29,923' immediate

*

ERROR at line 1:

ORA-00031: session marked for kill

session3：

8 RECOVERING            24503      10854      13649 06-DEC-21

session4：

8         15       5502 RECOVERING                10854           24503                    46                                  08000F007E150000                           0

session5：

SQL> select substr(username,1,10) username,sid,serial#,osuser,segment_name,used_ublk

  2  from v$transaction,dba_rollback_segs,v$session

  3  where SADDR=SES_ADDR and XIDUSN=SEGMENT_ID

  4  order by used_ublk;

USERNAME                    SID    SERIAL# OSUSER                         SEGMENT_NAME                    USED_UBLK

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

TEST123                      25       2163 oracle                         _SYSSMU2_349999951$                   872

SQL> col segment_name for a30

SQL> col start_time for a30

SQL> col program for a30

SQL> col sql_text for a50

SQL> col username for a20

SQL> select r.segment_name

  2  , t.used_ublk

  3  , t.start_time

  4  , se.username

  5  , se.sid

  6  , se.program

  7  , s.sql_text

  8  from dba_rollback_segs r, v$transaction t, v$session se, v$sql s

  9  where t.xidusn = r.segment_id

 10  and t.addr = se.taddr

 11  and se.sql_address = s.address

 12  and se.sql_hash_value = s.hash_value

 13  order by t.start_time;

SEGMENT_NAME                    USED_UBLK START_TIME                     USERNAME                    SID PROGRAM                        SQL_TEXT

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

_SYSSMU2_349999951$                   872 12/06/21 14:08:10              TEST123                      25 sqlplus@yikuer (TNS V1-V3)     delete from t1

参考文档：

How to Check the session and active SQL that consume the Rollback segments. (Doc ID 2625058.1)