UNDO的实验

[oracle@RHEL5 oracle]$ vi /home/oracle/mystat.sql
set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

[oracle@RHEL5 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jun 6 12:51:51 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             134219224 bytes
Database Buffers          142606336 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

SQL> create table big_table as select rownum id, a.* from all_objects a where 1=0;

Table created.

SQL> alter table big_table nologging;

Table altered.

SQL> alter table big_table add constraint big_table_pk primary key(id);

Table altered.

SQL> begin
dbms_stats.gather_table_stats
( wnname => user,
tabname => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade => TRUE );
end;
 /

PL/SQL procedure successfully completed.

 

SQL> create table t1 (x int) tablespace users;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> insert into t1
      select dbms_flashback.get_system_change_number
        from (select rownum from dba_objects where rownum <= 1000) a,
                 (select rownum from dba_objects where rownum <= 1000) b;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> update t1 set x = x + 1 ;

1000000 rows updated.

SQL> select xidusn,xidslot,xidsqn,flag from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN       FLAG
---------- ---------- ---------- ----------
         3         19        366       3587

◎　 xidusn：事务回滚段号
◎　 xidslot：事务表号
◎　 xidsqn：该事务表被第几次使用的事务序列号

SQL> select
          trunc(id1/power(2,16)) rbs,
          bitand(id1,to_number('ffff','xxxx'))+0 slot,
          id2 seq,
          lmode,request
     from v$lock,v$session
    where v$lock.type = 'TX'
      and v$lock.sid = v$session.sid
         and v$session.username is not null;

       RBS       SLOT        SEQ      LMODE    REQUEST
---------- ---------- ---------- ---------- ----------
         3         19        366          6          0

SQL> select name from v$rollname where usn =3;

NAME
------------------------------
_SYSSMU3$

SQL> select UBAFIL ,UBABLK   ,UBASQN   ,UBAREC, FLAG    from v$transaction;

    UBAFIL     UBABLK     UBASQN     UBAREC       FLAG
---------- ---------- ---------- ---------- ----------
         2      16626        525          4       3587

◎　UBAFIL是回滚段所在的文件

◎　UBABLK是事务记录的回滚段块数

◎　UBASQN是开始UBA时的事务序列号 ，见前面xidsqn

◎　UBAREC是用于回滚时的数据结构，是UBA记录数，１个UBAREC记录对应

一次回滚数据结构，所以它并不是DML一条记录就是一个UBAREC，下面来证明它

SQL> rollback;

Rollback complete.

SQL> create table t ( x int );

Table created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

SQL> select used_urec from v$transaction;

 USED_UREC
----------
         4

SQL> insert into t select rownum from all_users;

22 rows created.

SQL> select used_urec from v$transaction;

 USED_UREC
----------
         5

SQL> insert into t select rownum from all_objects;

49651 rows created.

SQL> select used_urec from v$transaction;

 USED_UREC
----------
       275

说明：１次执行１条，就增加１个UBAREC，１次执行1000条，可能也是增加１个UBAREC或者多个

UNDO管理模式实验

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_management = manual scope=spfile;

System altered.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/shujukuai/undotbs02.dbf' size 25m autoextend on next 1m maxsize 1024m;

Tablespace created.

SQL> alter system set undo_management = auto scope=spfile;

System altered.

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             142607832 bytes
Database Buffers          134217728 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS    WHERE TABLESPACE_NAME = 'UNDOTBS2';

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU11$                     ONLINE
_SYSSMU12$                     ONLINE
_SYSSMU13$                     ONLINE
_SYSSMU14$                     ONLINE
_SYSSMU15$                     ONLINE
_SYSSMU16$                     ONLINE
_SYSSMU17$                     ONLINE
_SYSSMU18$                     ONLINE
_SYSSMU19$                     ONLINE
_SYSSMU20$                     ONLINE

10 rows selected.

SQL> alter system set undo_tablespace = undotbs1;

System altered.

SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS where tablespace_name='UNDOTBS2';

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU11$                     OFFLINE
_SYSSMU12$                     OFFLINE
_SYSSMU13$                     OFFLINE
_SYSSMU14$                     OFFLINE
_SYSSMU15$                     OFFLINE
_SYSSMU16$                     OFFLINE
_SYSSMU17$                     OFFLINE
_SYSSMU18$                     OFFLINE
_SYSSMU19$                     OFFLINE
_SYSSMU20$                     OFFLINE

10 rows selected.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668611/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-668611/