收缩undo表空间(三)
PUBLIC._SYSSMU9$ UNDOTBS 131072 / 2 / 32765 ONLINE 11,665,408 13 114 368
--下面我们来先尝试收缩一下undo，收缩到23g，成功
goex_admin@CICCFIX> alter database datafile '/u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf' resize 23g;
Database altered.
--再次尝试收缩到10g出现错误提示，数据库中不存在事务，而undo表空间得不到释放
goex_admin@CICCFIX> alter database datafile '/u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf' resize 10g;
alter database datafile '/u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf' resize 10g
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
--下面新增一个新的undo表空间
goex_admin@CICCFIX> create undo tablespace undotbs2 datafile '/u02/database/CICCFIX/undo/undotbs_CICCFIX.dbf' size 100m autoextend on;
--设置系统undo表空间为新的表空间undotbs2
goex_admin@CICCFIX> alter system set undo_tablespace='UNDOTBS2' scope=both;
-->注，如果使用了pfile文件启动实例，此处会收到错误提示，可以省略scope参数，直接修改内存中的值，下次重启前需要相应修改pfile中的undo_tablespace
--观察undo段的变化情况，可以看到新的原来的undotbs表空间上的undo段全部处于OFFLINE状态，新的undo表空间为online状态
goex_admin@CICCFIX> @rollback_segments
Rollback Name Tablspace Init/Next Extents Min/Max Ex Status Bytes Extents Shrinks Wraps Opt. Size
------------------ ----------- -------------------- ---------- -------- ---------------- ------- -------- -------- ------------
SYS.SYSTEM SYSTEM 114688 / 1 / 32765 ONLINE 393,216 6 0 0
PUBLIC._SYSSMU1$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU10$ UNDOTBS 131072 / 2 / 32765 OFFLINE 3,276,800 5
PUBLIC._SYSSMU11$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU12$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU13$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU14$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU15$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU16$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU17$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU18$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU2$ UNDOTBS2 131072 / 2 / 32765 ONLINE 131,072 2 0 0
PUBLIC._SYSSMU21$ UNDOTBS 131072 / 2 / 32765 OFFLINE 4,325,376 6
PUBLIC._SYSSMU22$ UNDOTBS 131072 / 2 / 32765 OFFLINE 20,054,016 21
PUBLIC._SYSSMU3$ UNDOTBS 131072 / 2 / 32765 OFFLINE 4,325,376 6
PUBLIC._SYSSMU4$ UNDOTBS 131072 / 2 / 32765 OFFLINE 4,325,376 6
PUBLIC._SYSSMU5$ UNDOTBS 131072 / 2 / 32765 OFFLINE 4,325,376 6
PUBLIC._SYSSMU6$ UNDOTBS 131072 / 2 / 32765 OFFLINE 5,373,952 7
PUBLIC._SYSSMU7$ UNDOTBS 131072 / 2 / 32765 OFFLINE 9,568,256 11
PUBLIC._SYSSMU8$ UNDOTBS 131072 / 2 / 32765 OFFLINE 4,325,376 6
PUBLIC._SYSSMU9$ UNDOTBS 131072 / 2 / 32765 OFFLINE 11,665,408 13
--删除之前