Oracle回滚(ROLLBACK)和撤销(UNDO)(五)
提示
SQL> /
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO2' is currently in use
SQL> SHOW PARAMETER undo --查看的确是已切换到undotbs1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs1
--在session2中再次执行表空间切换到undotbs1
SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';
System altered.
--此时undo2成功删除，可以看出需要在将活动事务提交或回滚后，再切换之后才能成功删除撤销表空间
SQL> DROP TABLESPACE undo2;
Tablespace dropped.
SQL> ho ls $ORACLE_BASE/oradata/orcl;
control01.ctl redo03.log redo2.log system01.dbf undotbs02.dbf
control02.ctl redo04.log redo3.log tbs1_1.dbf users01.dbf
example01.dbf redo07.log redo7.log tbs1_2.dbf
redo01.log redo08.log redo8.log temp01.dbf
redo02.log redo1.log sysaux01.dbf undotbs01.dbf
--删除UNDO表空间的物理文件
SQL> ho rm $ORACLE_BASE/oradata/orcl/undotbs02.dbf;
--在session1中可以看到两条记录也被成功插入
SQL> SELECT * FROM tb_test;
ID NAME
---------- --------------------
1 Robinson
2 Jack
--查看当前撤销表空间的大小
SQL> SELECT tablespace_name,bytes/1024/1024 FROM dba_data_files
2 WHERE tablespace_name = 'UNDOTBS1';
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
UNDOTBS1 30
--循环插入记录到tb_test后查看undo表空间的使用情况
SQL> BEGIN
2 FOR i IN 1..20000
3 LOOP
4 INSERT INTO tb_test VALUES(i,'Unkown Name');
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
--可以看到UNDO 表空间只用了个块
SQL> SELECT addr,xidusn,used_ublk FROM v$transaction;
ADDR XIDUSN USED_UBLK
-------- ---------- ----------
2D9FC160 6 174
SQL> SELECT 174 * 8 || 'KB' FROM dual;
174*8|
------
1392KB
五、计算UNDO表空间的大小
计算公式：
MAX(undoblks)/600 * MAX(maxquerylen) 位于v$un
dostat
* db_block_size 位于v$parameter
--创建演示环境
SQL> INSERT INTO tb_test SELECT employee_id,first_name FROM hr.employees;
107 rows created
SQL> INSERT INTO tb_test SELECT * from tb_test;
109 rows created.
--多次执行上述命令，下面是的tb_test表中的记录数
SQL> /
892928 rows created.
SQL> COMMIT;
Commit complete.
--查看当前undo表空间的大小
SQL> SELECT t.name,d.name,d.bytes/1024/1024 as TotalSize ,t.flashback_on,d.status
2 FROM v$tablespace t
3 JOIN v$datafile d
4 USING (ts#)
5 WHERE t.name LIKE 'UNDO%';
NAME NAME TOTALSIZE FLA STATUS
--------------------------------- ------------------------------------------- ---------- --- -------
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 30 YES ONLINE
--将undo表空间修改为RETENTI