- 最后登录
- 2012-6-24
- 在线时间
- 17 小时
- 威望
- 58
- 金钱
- 390
- 注册时间
- 2012-4-23
- 阅读权限
- 50
- 帖子
- 23
- 精华
- 1
- 积分
- 58
- UID
- 384
|
1#
发表于 2012-4-24 22:43:22
|
查看: 6143 |
回复: 5
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 |
|