session1SQL> 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 923SQL> SQL> delete from t1;delete from t1*ERROR at line 1:ORA-00028: your session has been killedORA-00028: your session has been killedsession2SQL> alter system kill session '29,923' immediate;alter system kill session '29,923' immediate*ERROR at line 1:ORA-00031: session marked for killsession3：8 RECOVERING 24503 10854 13649 06-DEC-21session4：8 15 5502 RECOVERING 10854 24503 46 08000F007E150000 0session5：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$ 872SQL> col segment_name for a30SQL> col start_time for a30SQL> col program for a30SQL> col sql_text for a50SQL> col username for a20SQL> 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)
