INSERT：

 UPDATE:

 DELETE：

 --查看本机中Oracle的版本

SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
--查看和UNDO相关的参数

SQL> SHOW PARAMETER undo;
NAME                                              TYPE             VALUE
------------------------------------     -----------    ------------------------------
undo_management                   string              MANUAL
undo_retention                            integer             900
undo_tablespace                       string             UNDOTBS1
undo_management：

undo_retention：

undo_tablespace：

 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
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$                             UNDOTBS1                              ONLINE
_SYSSMU5$                             UNDOTBS1                              ONLINE
_SYSSMU6$                            UNDOTBS1                              ONLINE
_SYSSMU7$                            UNDOTBS1                              ONLINE
_SYSSMU8$                            UNDOTBS1                              ONLINE
_SYSSMU9$                            UNDOTBS1                              ONLINE
_SYSSMU10$                          UNDOTBS1                              ONLINE

--查看DML语句产生的事务

SQL> SHOW USER;
USER is "SYS"
SQL> SELECT * FROM scott.emp WHERE ename = 'SCOTT';
EMPNO     ENAME            JOB                 MGR        HIREDATE        SALARY        DEPTNO
----------     ---------------    ---------             ----------        ---------              ----------          ----------
7788        SCOTT           ANALYST         7566       19-APR-87           3500               20
SQL> UPDATE scott.emp SET sal = sal * 2 WHERE  ename = 'SCOTT';
1 row updated.
SQL> SELECT addr,xidusn,status,start_time,used_ublk FROM v$transaction;
ADDR               XIDUSN    STATUS           START_TIME            USED_UBLK
--------              ----------    ----------------         --------------------            ----------
2DA2B17C        9             ACTIVE           07/10/10 20:29:08             1
--查看当前哪些

用户使用撤销段以及段的大小，启动时间，活动状态等
SQL> SELECT t.xidusn, t.start_time, t.used_ublk, t.status,
s.username,r.segment_name
FROM v$transaction t
JOIN v$session s
ON t.ses_addr = s.saddr
JOIN dba_rollback_segs r
ON r.segment_id = t.xidusn ;
XIDUSN       START_TIME            USED_UBLK            STATUS                USERNAME                        SEGMENT_NAME
----------         --------------------                 ---------                  ----------------     ------------------------------                   -------------
9          07/10/10 20:29:08             1                             ACTIVE                     SYS                                      _SYSSMU9$

 CREATE UNDO TABLESPACE tablespace_name DATAFILE '...' SIZE n