Oracle回滚(ROLLBACK)和撤销(UNDO)(二)
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
TABLE EXAMPLE
TABLE PARTITION EXAMPLE
INDEX SYSAUX
INDEX PARTITION SYSAUX
LOB PARTITION SYSAUX
LOBINDEX SYSAUX
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
LOBSEGMENT SYSAUX
NESTED TABLE SYSAUX
TABLE SYSAUX
TABLE PARTITION SYSAUX
CLUSTER SYSTEM
INDEX SYSTEM
LOBINDEX SYSTEM
LOBSEGMENT SYSTEM
NESTED TABLE SYSTEM
ROLLBACK SYSTEM --与之前的版本兼容的回滚段
TABLE SYSTEM
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
TABLE TBS1
TYPE2 UNDO UNDOTBS1 --9i之后使用的撤销段
INDEX USERS
LOBINDEX USERS
LOBSEGMENT USERS
NESTED TABLE USERS
TABLE USERS
--下面将undo_management改为支持自动管理，需要重启实例
SQL> ALTER SYSTEM SET undo_management = 'auto' SCOPE = SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 67110676 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
--再次查看dba_rollback_segs视图所有的撤销段全部处于online状态
--注意第一行为system表空间的撤销段，用于系统表空间的撤销
SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$