关于Undo的实验

1、虽然Undo中没有active的数据，在resize的时候还是会不能小于当前大小。【只扩展，不回收】

SQL> @show_undo_usage.sql

Tablespace Name      Status       Used Size    Tablespace Size Used Pct
-------------------- ------------ ------------ --------------- --------
UNDOTBS1             EXPIRED                44             760 5.83%
                     UNEXPIRED              15             760 1.99%
********************              ------------
Total:                                      59


SQL> alter database datafile '/oracle/app/oracle/oradata/ocm/undotbs01.dbf' resize 100M;
alter database datafile '/oracle/app/oracle/oradata/ocm/undotbs01.dbf' resize 100M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> alter database datafile '/oracle/app/oracle/oradata/ocm/undotbs01.dbf' resize 800M;

Database altered.

2、当存在多个undo表空间的时候，一个空间不够了，会不会使用另外一个undo

SQL> create undo tablespace UNDOTBS2 datafile '/oracle/app/oracle/oradata/ocm/undotbs02.dbf' size 100M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> set line 400 pages 400
SQL> /

       USN    XACTS Status       RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- ------------ --------------------- ---------------------- ----------
    15        0 ONLINE              .000114441         .000114441      0
    11        0 ONLINE              .000114441         .000114441      0
    12        0 ONLINE              .000114441         .000114441      0
    13        0 ONLINE              .000114441         .000114441      0
    14        0 ONLINE              .000114441         .000114441      0
    20        0 ONLINE              .000114441         .000114441      0
    16        0 ONLINE              .000114441         .000114441      0
    17        0 ONLINE              .000114441         .000114441      0
    18        0 ONLINE              .000114441         .000114441      0
    19        0 ONLINE              .000114441         .000114441      0
     0        0 ONLINE              .000358582         .000358582      0

11 rows selected.

SQL> alter tablespace undotbs1 offline;

Tablespace altered.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

       USN    XACTS Status       RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- ------------ --------------------- ---------------------- ----------
    15        0 ONLINE              .000114441         .000114441      0
    11        0 ONLINE              .000114441         .000114441      0
    12        0 ONLINE              .000114441         .000114441      0
    13        0 ONLINE              .000114441         .000114441      0
    14        0 ONLINE              .000114441         .000114441      0
    20        0 ONLINE              .000114441         .000114441      0
    16        0 ONLINE              .000114441         .000114441      0
    17        0 ONLINE              .000114441         .000114441      0
    18        0 ONLINE              .000114441         .000114441      0
    19        0 ONLINE              .000114441         .000114441      0
     0        0 ONLINE              .000358582         .000358582      0

11 rows selected.

• 模拟对大表的修改，查看当存在多个undo时，是否会使用另外一个undo

SQL> update TEMP_TEST set object_name='dayu';
update TEMP_TEST set object_name='dayu'
       *
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'




Tablespace Name      Status         Used Size Tablespace Size Used Pct
-------------------- ------------ ------------ --------------- --------
UNDOTBS2             ACTIVE                98              100 97.88%
                     EXPIRED               1               100 .56%
                     UNEXPIRED             1               100 .56%
UNDOTBS3             EXPIRED               1               100 .63%
                     UNEXPIRED             1               100 .63%
********************               ------------
Total:                                      100


***********
在没有报错的时候，等待事件如下
***********
SQL_ID          EVENT
------------- ----------------------------------------------------------------
dctwkdqtnvrf9 SQL*Net message to client
054jqgavyxu4w statement suspended, wait error to