ORACLE数据库只有一个当前UNDO有空间。
1.查看当前UNDO表空间
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
查询还原段数据的视图
V$rollname
v$rollstat
v$session
v$transaction
dba_rollback_segs
SQL> select to_char(begin_time,'HH:MM:SS') begin_time,to_char(end_time,'hh:mm:s') end_time,undoblks,txncount,maxquerylen from v$undostat;
BEGIN_TI END_TIME UNDOBLKS TXNCOUNT MAXQUERYLEN
-------- -------- ---------- ---------------------
02:01:41 03:01:29 78 112 681
02:01:41 02:01:41 12 113 376
02:01:41 02:01:41 11 120 973
02:01:41 02:01:41 6 72 364
02:01:41 02:01:41 10 98 959
02:01:41 02:01:41 9 67 349
01:01:41 02:01:41 26 129 944
01:01:41 01:01:41 6 67 336
01:01:41 01:01:41 58 511 927
01:01:41 01:01:41 20 142 319
2.创建UNDO表空间
SQL> create undo tablespace test1_undo datafile 'f:\sysdata\test1_undo.dbf' size 2m;
Tablespace created
SQL> col file_name for a30
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME MB
---------- ------------------------------ ------------------------------ ----------
5 F:\SYSDATA\TEST1_UNDO.DBF TEST1_UNDO 2
4 F:\USERDATA\USERS01.DBF USERS 6.25
3 F:\SYSDATA\UNDOTBS01.DBF UNDOTBS1 85
2 F:\SYSDATA\SYSAUX01.DBF SYSAUX 760
1 F:\SYSDATA\SYSTEM01.DBF SYSTEM 770
SQL> select tablespace_name,block_size,status,contents,segment_space_management from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS SEGMENT_SPACE_MANAGEMENT
------------------------------ ---------- --------- --------- ------------------------
SYSTEM 8192 ONLINE PERMANENT MANUAL
SYSAUX 8192 ONLINE PERMANENT AUTO
UNDOTBS1 8192 ONLINE UNDO MANUAL
TEMP 8192 ONLINE TEMPORARY MANUAL
USERS 8192 ONLINE PERMANENT AUTO
TEST1_UNDO 8192 ONLINE UNDO MANUAL
3.切换UNDO表空间并验证
SQL> alter system set undo_tablespace=test1_undo;
System altered
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string TEST1_UNDO
删除：--需要切换回原UNDO表空间后。
10:44:14 SQL> drop tablespace test1_undo including contents;
