|
|
原帖由 Yong Huang 于 2008-6-6 04:26 发表 ![]()
Did you alter system set "_smu_debug_mode"=[some value]? I don't know all the implications of that parameter?
Maybe we missed the basic stuff. A simple "delete from t9 where id=1" could require a lot of undo. For instance, you have millions or billions of rows matching id=1, and possibly index entries. So if the total undo space is very small, even if this is the only transaction, the undo space is still not enough.
What if you try delete from t9 where rownum=1 in your first experiment?
Yong Huang
Yes, I did set "_smu_debug_mode"=45 as you mentioned in another thread and if not:
SQL> set transaction use rollback segment "_SYSSMU11$";
set transaction use rollback segment "_SYSSMU11$"
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
It seems that the '"smu_debug_mode"=45' and 'set transaction use rollback segment "_SYSSMU11$";' results in the output:
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
---------- ---------- ---------- ---------- ---------- ----------
11 16 6 0 0 0
--------------------------------------------------------------------------------------------------------------------------------------------------
Then I did the test in normal situation:
SQL>startup force;
SQL> select usn,rssize,hwmsize from v$rollstat;
USN RSSIZE HWMSIZE
---------- ---------- ----------
0 385024 385024
11 122880 122880
12 122880 122880
13 188416 188416
14 122880 188416
15 122880 122880
16 122880 122880
17 122880 122880
已选择8行。
SQL> delete from dengsk.t9 where id=1;
delete from dengsk.t9 where id=1
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO02'
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
no rows selected
SQL> select usn,rssize,hwmsize from v$rollstat;
USN RSSIZE HWMSIZE
---------- ---------- ----------
0 385024 385024
11 122880 122880
12 188416 188416
13 122880 188416
14 122880 188416
15 122880 122880
16 122880 122880
17 122880 122880
已选择8行。
SQL>
'delete from t9 where rownum=1' can surely succeed. A big tranaction would require more undo blocks than the undo segment affords. |
|
楼主: solearn
IP卡
狗仔卡
发表于 2008-6-5 12:41
显身卡
楼主
发表于 2008-6-6 09:24
racleadmin estdbudump est_ora_3288.trc
9.7) 2008-06-06 10:00:53.000