Oracle数据库回滚段操作

来源：互联网发布：销售数据分析系统编辑：程序博客网时间：2024/05/20 01:37

1.新建undo表空间
SQL> create undo tablespace undotbs2 datafile 'E:\oracle\product\oradata\orcl\un
dotbs02.dbf' size 10M autoextend on next 10M maxsize 500M;

2.由于undo_tablespace是个动态参数，所以任何时候都可以set。
SQL> alter system set undo_tablespace='undotbs2' scope=both;
系统已更改。

3.查看原来的undo表空间中的undo segment是否都为offline
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS2 _SYSSMU11$ ONLINE
UNDOTBS2 _SYSSMU12$ ONLINE
UNDOTBS2 _SYSSMU13$ ONLINE
UNDOTBS2 _SYSSMU14$ ONLINE
UNDOTBS2 _SYSSMU15$ ONLINE
UNDOTBS2 _SYSSMU16$ ONLINE
UNDOTBS2 _SYSSMU17$ ONLINE
UNDOTBS2 _SYSSMU18$ ONLINE
UNDOTBS2 _SYSSMU19$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ ONLINE
21 rows selected
原来undotbs1的undo_segment 都为offline，则可以删除undotbs1.
SQL> drop tablespace undotbs1 including contents and datafiles;
表空间已删除。
再次查看：
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS2 _SYSSMU11$ ONLINE
UNDOTBS2 _SYSSMU12$ ONLINE
UNDOTBS2 _SYSSMU13$ ONLINE
UNDOTBS2 _SYSSMU14$ ONLINE
UNDOTBS2 _SYSSMU15$ ONLINE
UNDOTBS2 _SYSSMU16$ ONLINE
UNDOTBS2 _SYSSMU17$ ONLINE
UNDOTBS2 _SYSSMU18$ ONLINE
UNDOTBS2 _SYSSMU19$ ONLINE
UNDOTBS2 _SYSSMU20$ ONLINE
11 rows
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2
由于没有offline undotbs1，所以物理删除数据文件的时候报错：
E:\oracle\product\oradata\orcl>del undotbs01.dbf
E:\oracle\product\oradata\orcl\UNDOTBS01.DBF
另一个程序正在使用此文件，进程无法访问。
将oracle的服务关闭后，可以删除。
重新切换，这次先offline，再drop。
SQL> create undo tablespace undotbs1 datafile 'E:\oracle\product\oradata\orcl\undotbs01.dbf' size 10M autoextend on next 10M maxsize 500M;
Tablespace created
SQL> alter system set undo_tablespace='undotbs1' scope=both;
System altered
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS2 _SYSSMU11$ OFFLINE
UNDOTBS2 _SYSSMU12$ OFFLINE
UNDOTBS2 _SYSSMU13$ OFFLINE
UNDOTBS2 _SYSSMU14$ OFFLINE
UNDOTBS2 _SYSSMU15$ OFFLINE
UNDOTBS2 _SYSSMU16$ OFFLINE
UNDOTBS2 _SYSSMU17$ OFFLINE
UNDOTBS2 _SYSSMU18$ OFFLINE
UNDOTBS2 _SYSSMU19$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ OFFLINE
21 rows selected
SQL> alter tablespace undotbs2 offline;
Tablespace altered
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
11 rows selected
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs1

0 0