Oracle回滚(ROLLBACK)和撤销(UNDO)(四)
EATE UNDO TABLESPACE undo2
2 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 3M
3 AUTOEXTEND ON;
Tablespace created.
SQL> SELECT file_name,tablespace_name FROM dba_data_files WHERE tablespace_name LIKE 'UNDO%';
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/undotbs02.dbf UNDO2
--查看当前系统使用的UNDO表空间为UNDOTBS1
SQL> SELECT name,value FROM v$parameter WHERE name LIKE 'undo%';
NAME VALUE
------------------------------ --------------------------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
--创建一张表tb_test用于演示，假定该会话为session1
SQL> CREATE TABLE tb_test
2 (
3 ID INT,
4 Name VARCHAR2(20)
5 );
Table created.
--插入一条记录到tb_test，此时未提交将产生UNDO 信息
SQL> INSERT INTO tb_test SELECT 1,'Robinson' FROM dual;
1 row created.
--此时打开另外一个回话，假定为session2，在session2中切换表空间
SQL> ALTER SYSTEM SET undo_tablespace = 'undo2';
System altered. --undotbs1中有未提交的事务，竟然可以成功切换？如此这般闪回时估计会有问题
SQL> SHOW PARAMETER undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undo2
--在session1中执行commit
SQL> COMMIT;
Commit complete. --成功执行了commit,且下面的查询看到了提交后的结果
SQL> SELECT * FROM tb_test;
ID NAME
---------- ------------------------------
1 Robinson
--登出系统后再次查看，结果依然存在，比较纳闷
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[uniread] Saved history (716 lines)
[oracle@robinson ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 10 21:29:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT * FROM tb_test;
ID NAME
---------- --------------------
1 Robinson
--删除UNDO表空间
--在session1中插入一条新记录
SQL> INSERT INTO tb_test SELECT 2,'Jack' FROM DUAL;
1 row created.
--在session2中将撤销表空间切换为undotbs1
SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';
System altered.
--紧接着在该回话中删除undo2，提示正在使用
SQL> DROP TABLESPACE undo2;
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO2' is currently in use
--在session1中提交事务
SQL> COMMIT;
Commit complete.
--在session2中再次删除表空间undo2，收到了相同的错误