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)
