关于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