retention guarantee 子句:
保证撤销保留,使用下面的操作来实现
ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;
--下面的查询中是当 undo_management 为 manual 时的结果集,可以看出撤销表空间的撤销
段都处于
offline 状态
SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
--在 undo_management 参数为 manual 时,对 scott.emp 插入一条新记录,收到了错误提示
--非系统表空间不能够使用回滚段
SQL> INSERT INTO scott.emp(empno,ename,salary)
2 VALUES(6666,'Jenney',3000);
INSERT INTO scott.emp(empno,ename,salary)
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
--查看段的类型,发现仅仅 system 表空间存在 ROLLBACK 段,所以前一条插入语句收到
错误提示
SQL> SELECT DISTINCT segment_type,tablespace_name FROM dba_segments
2 ORDER BY tablespace_name;
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
INDEX EXAMPLE
INDEX PARTITION EXAMPLE
LOBINDEX EXAMPLE
LOBSEGMENT EXAMPLE
NESTED TABLE EXAMPLE