switch UNDO

orasupp

session 1:SQL> SHOW PARAMETER UNDO

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS2


SQL> select sid from v$mystat where rownum=1;

       SID
----------
      1998


SQL> delete from TEST;

11053890 rows deleted.

----------------------------------------没有COMMIT;


session 2:

SQL> alter system set undo_tablespace=UNDOTBS1 scope=both sid='rac1';

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS1



SQL> drop tablespace UNDOTBS2 including contents AND DATAFILES;
drop tablespace UNDOTBS2 including contents AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use


SQL> select vs.sid, vt.used_ublk,vt.used_urec,vt.start_time,vt.log_io,vt.phy_io,vt.CR_get,vt.CR_change from v$transaction vt, v$session vs where vt.addr = vs.taddr;

       SID  USED_UBLK  USED_UREC START_TIME               LOG_IO     PHY_IO     CR_GET  CR_CHANGE
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ----------
      1998     241135   11053890 04/24/12 22:14:51      48984925     198912    3928603    3834497       -------session 1 undo 信息


SQL> alter system kill session '1998,624';

System altered.

SQL> select vs.sid, vt.used_ublk,vt.used_urec,vt.start_time,vt.log_io,vt.phy_io,vt.CR_get,vt.CR_change from v$transaction vt, v$session vs where vt.addr = vs.taddr;

no rows selected

SQL> drop tablespace UNDOTBS2 including contents AND DATAFILES;
drop tablespace UNDOTBS2 including contents AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use




因为session 1做了大量DELETE,即使在SESSION 2把SESSION 1 KILL了，SESSION 1 应该还要ROLLBACK,v$transaction为什么查询不到ROLLBACK的信息呢？怎样查询是哪个SESSION还在用UNDOTBS2呢？
select * from dba_rollback_segs where tablespace_name='UNDOTBS2';
SEGMENT_NAME
OWNER
TABLESPACE_NAME
SEGMENT_ID
FILE_ID
BLOCK_ID
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
STATUS
INSTANCE_NUM
RELATIVE_FNO
_SYSSMU11$
PUBLIC
UNDOTBS1_V1
11
5
9
131072
2
32765
OFFLINE
1
5
_SYSSMU12$
PUBLIC
UNDOTBS1_V1
12
5
25
131072
2
32765
OFFLINE
1
5
_SYSSMU13$
PUBLIC
UNDOTBS1_V1
13
5
41
131072
2
32765
OFFLINE
1
5
_SYSSMU14$
PUBLIC
UNDOTBS1_V1
14
5
57
131072
2
32765
ONLINE
1
5
_SYSSMU15$
PUBLIC
UNDOTBS1_V1
15
5
73
131072
2
32765
OFFLINE
1
5
_SYSSMU16$
PUBLIC
UNDOTBS1_V1
16
5
89
131072
2
32765
OFFLINE
1
5
_SYSSMU17$
PUBLIC
UNDOTBS1_V1
17
5
105
131072
2
32765
ONLINE
1
5              －－－－UNDOTBS2 的UNDO SEGMENT _SYSSMU17$是ONLINE的
_SYSSMU18$
PUBLIC
UNDOTBS1_V1
18
5
121
131072
2
32765
OFFLINE
1
5
_SYSSMU19$
PUBLIC
UNDOTBS1_V1
19
5
137
131072
2
32765
OFFLINE
1
5
_SYSSMU20$
PUBLIC
UNDOTBS1_V1
20
5
153
131072
2
32765
OFFLINE
1
5

Maclean Liu(刘相兵

关键词 X$KTUXE

View:   X$KTUXE
         [K]ernel [T]ransaction [U]ndo
           Transa[x]tion [E]ntry (table)

  This view is very useful as it gives an indication of the state
  of the transaction tables in the rollback segment headers.
  The information here can be used to see the state of transactions
  requiring transaction recovery do not show in <View:V$TRANSACTION>
  <Event:10013> may be useful to trace transaction recovery.

Column          Type               Description
--------        ----               --------
ADDR            RAW(4|8)           address of this row/entry in the array or SGA
INDX            NUMBER             index number of this row in the fixed table array
INST_ID         NUMBER       8.x   oracle instance number

Transaction ID
KTUXEUSN        NUMBER             undo seg number
       KUSNOLTP UB2MAXVAL           is no-undo xac
KTUXESLT        NUMBER             slot number
       KSLTINV  UB2MAXVAL           not a valid slot num
KTUXESQN        NUMBER             wrap number, is savept # at start if no-undo xac
                                     position within transaction

KTUXERDBF       NUMBER             relative File
KTUXERDBB       NUMBER             relative Block
KTUXESCNB       NUMBER             SCN base for prepare/commit
KTUXESCNW       NUMBER             SCN wrap for prepare/commit
KTUXESTA        VARCHAR2(16)       Transaction Status
KTUXECFL        VARCHAR2(24)       Transaction flags
KTUXEUEL        NUMBER             Used for extent of tx and link to commit list

Distributed tx: collecting dba and undo bk to start retrieving collecting info rec
KTUXEDDBF       NUMBER             relative file
KTUXEDDBB       NUMBER             relative dba

Parent transaction id
KTUXEPUSN       NUMBER       8.x   undo seg number
       KUSNOLTP UB2MAXVAL           is no-undo xac
KTUXEPSLT       NUMBER       8.x   slot number
       KSLTINV  UB2MAXVAL           not a valid slot num
KTUXEPSQN       NUMBER       8.x   wrap number, is savept # at start if no-undo xac
                                      position within transaction

KTUXESIZ        NUMBER       8.1  number of undo blocks used by the transaction

Notes:

To see any DEAD transactions for deferred transaction recovery
  after startup:

      select * from x$ktuxe where ktuxecfl='DEAD';



http://www.oracledatabase12g.com ... on-entry-table.html

Maclean Liu(刘相兵

session A :

delete larges ;

session B  :

SQL> alter system kill session '159,1187';

System altered.

SQL> select * from v$transaction;

no rows selected



SQL>  select * from x$ktuxe where ktuxecfl='DEAD';

ADDR                   INDX    INST_ID   KTUXEUSN   KTUXESLT   KTUXESQN  KTUXERDBF  KTUXERDBB  KTUXESCNB  KTUXESCNW KTUXESTA
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
KTUXECFL                   KTUXEUEL  KTUXEDDBF  KTUXEDDBB  KTUXEPUSN  KTUXEPSLT  KTUXEPSQN   KTUXESIZ
------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007FB4CEA26BF0        527          1          9         45        417          2      26489    1025033          0 ACTIVE
DEAD                              0          0          0          0          0          0      25516

SQL> /

ADDR                   INDX    INST_ID   KTUXEUSN   KTUXESLT   KTUXESQN  KTUXERDBF  KTUXERDBB  KTUXESCNB  KTUXESCNW KTUXESTA
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
KTUXECFL                   KTUXEUEL  KTUXEDDBF  KTUXEDDBB  KTUXEPUSN  KTUXEPSLT  KTUXEPSQN   KTUXESIZ
------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
00007FB4CEA22C00        527          1          9         45        417          2      26166    1025033          0 ACTIVE
DEAD                              0          0          0          0          0          0      25201


KTUXESIZ   number of undo blocks used by the transaction ，KTUXESIZ 缩小说明 该 dead transaction 正在被rollback

orasupp

SQL> select KTUXESIZ from X$KTUXE where ktuxecfl='DEAD';

  KTUXESIZ
----------
         0

SQL>  select *  from X$KTUXE where ktuxecfl='DEAD';

ADDR                   INDX    INST_ID   KTUXEUSN   KTUXESLT   KTUXESQN  KTUXERDBF  KTUXERDBB  KTUXESCNB  KTUXESCNW KTUXESTA         KTUXECFL                   KTUXEUEL  KTUXEDDBF  KTUXEDDBB
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ------------------------ ---------- ---------- ----------
KTUXEPUSN  KTUXEPSLT  KTUXEPSQN   KTUXESIZ
---------- ---------- ---------- ----------
0000002A974E5590        423          1         17         37   14632883          0          0 2790453970         50 INACTIVE         DEAD                             39          0          0
         0          0          0          0

那有个transaction  KTUXESIZ 一直等于0是什么意思呢？

Maclean Liu(刘相兵

session A:

SQL> delete large;


session B:


SQL> select sum(bytes / 1024 / 1024), status, tablespace_name
  2  from dba_undo_extents
  3  group by status, tablespace_name;

SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME
-------------------- --------- ------------------------------
               .9375 EXPIRED   UNDOTBS1
             11.3125 UNEXPIRED UNDOTBS1
           1616.6875 ACTIVE    UNDOTBS1
                  
SQL> alter system kill session '148,279';                  
                  
SQL> set linesize 140 pagesize 1400

SQL> select KTUXESIZ from X$KTUXE where ktuxecfl='DEAD';

  KTUXESIZ
----------
    229372

SQL> /

  KTUXESIZ
----------
    228810

SQL>  select sum(bytes / 1024 / 1024), status, tablespace_name
  2      from dba_undo_extents
  3      group by status, tablespace_name;

SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME
-------------------- --------- ------------------------------
               .9375 EXPIRED   UNDOTBS1
             11.3125 UNEXPIRED UNDOTBS1
           1876.6875 ACTIVE    UNDOTBS1
                  


SQL> select KTUXESIZ from X$KTUXE where ktuxecfl='DEAD';

  KTUXESIZ
----------
    209580

SQL> /

  KTUXESIZ
----------
    209230

SQL> /

  KTUXESIZ
----------
    209217

SQL> /

  KTUXESIZ
----------
    197904

SQL> /

  KTUXESIZ
----------
    196906

SQL> /

  KTUXESIZ
----------
    196550

SQL> /

  KTUXESIZ
----------
         0

SQL> select KTUXESIZ from X$KTUXE where ktuxecfl='DEAD';

  KTUXESIZ
----------
         0

SQL>  select sum(bytes / 1024 / 1024), status, tablespace_name
  2      from dba_undo_extents
  3      group by status, tablespace_name;

SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME
-------------------- --------- ------------------------------
                9.25 EXPIRED   UNDOTBS1
           1879.6875 UNEXPIRED UNDOTBS1                  

KTUXESIZ =0 说明 dead transaction 被SMON rollback完成， 相关的 undo extent 不再是active状态

orasupp

got it,thanks a lot!