oracle10g undo tablespace相关知识
undo tablespace
概念：
1，用于恢复事务
2，一致性查询
3，oracle闪回查询及闪回事务
如果数据库没有undo tablespace，数据库可以启动，但会使用system表空间存储undo data;
undo_rerention
概念：
1，用于oracle闪回查询及闪回事务
2，长事务查询，因为会用到一致性块；（注：虽然事务已提交）
3，undo_retention尽量大一些
如果开启自动撤消管理模式，即存在一个undo retention period;
undo retention period：
概念：
1，单位是秒
2，重写撤消空间之前撤消数据的最小保存时间；
3，基于这个时间，如果已commit的撤消数据老于当前的undo retention period，则其状态为：expired
4, 同上，如少于当前的undo retention period，则状态为unexpired;
5，它的大小，oracle会自动根据undo tablespace大小和数据库活动情况自动进行调节；
当然，也可以手工以undo_rentention指定最小的undo retention period;
6,只要新的事务能从undo tablespace分配到空间，oracle会尽力基于undo retention period保存undo data；
如果undo tablespace空间不足，先重写expired undo data,发现空间仍不足，重写unexpired undo data;
以上2者还是不能解决空间问题，报snapshot too old错误
与undo_retention参数有关的几个要点：
1，如果undo tablespace不是自动扩展，即固定大小的文件，数据库如空间不足，直接重写unexpired undo data;
2,如果undo tablespace自动扩展，会根据undo_retention尽力保存undo data;如空间不足，则先自动扩展；
如指定了maxsize，且达到maxsize，则重写unexpired undo data;
retention guarantee
概念：
1，为了保证一些长查询或者闪回操作的顺利进行，可以启动此功能
2，启用此功能后，即便undo tablespace空间不足，也不会重写unexpired undo data,一般默认不开启；
3，如果启用此功能，则可能导致很多dml失败；所以使用一定要小心
4，有多种方式启用此功能：
1，create database
2,create undo tablespace
3,alter tablespace
5，用dba_tablespaces可以查看此功能是否启用
1,dba_tablespace的retention列：
值为：not apply用于非undo tablespace
guarantee用于undo tablespace
noguarantee同上
---undo tablespace数据文件是自动扩展
select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='UNDOTBS1'
FILE_NAME TABLESPACE_NAME AUT
-------------------------------------------------- ------------------------------ ---
/oracle10g/oradata/first/undotbs01.dbf UNDOTBS1 YES
SQL> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
undo retention自动调节
1,如undo tablespace固定大小非自动扩展，数据库会根据系统负荷及undo tablespace大小，调节undo retention大小；
调节的undo retention明显要大于undo_retention参数的值
2,如undo tablespace自动扩展，数据库会基于系统当前时间最长查询来调节undo retention，其undo retention的值稍大于最长查询所占用的
时间；而且，调节的undo retention的值可能大于undo_retenion参数的值
3，此功能不适用于lob数据类型，因为lob列把undo存储在它独立的segment，而不会存储在undo tablespace中；
对于lob,oracle尽力根据undo_retention保存undo data,但若空间不足，会重写lob unexpired undo data;
调节的undo retention值可从如下视图获知，采样间隔为10分钟；仅存储4天的数据，若超过4天，则存储在dba_hist_undostat中
SQL> select to_char(begin_time,'yyyymmdd hh24:mi:ss') begin_time,to_char(end_time,'yyyymmdd hh24:mi:ss') end_time,tuned_undoretention from v$undostat order by 1;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
----------------- ----------------- -------------------
20130708 10:17:03 20130708 10:27:03 900
20130708 10:27:03 20130708 10:37:03 900
20130708 10:37:03 20130708 10:47:03 900
20130708 10:47:03 20130708 10:57:03 900
20130708 10:57:03 20130708 11:07:03 900
20130708 11:07:03 20130708 11:17:03 900
20130708 11:17:03 20130708 11:27:03 900
20130708 11:27:03 20130708 11:37:03 900
20130708 11:37:03 20130708 11:47:03 900
20130708 11:47:03 20130708 11:57:03 900
20130708 11:57:03 20130708 12:07:03 900
BEGIN_TIME END_TIME TUNED_UNDORETENTION
----------------- ----------------- -------------------
20130708 12:07:03 20130708 12:17:03 900
20130708 12:17:03 20130708 13:17:03 0
20130708 13:17:03 20130708 13:27:03 900
20130708 13:27:03 20130708 13:37:03 900
20130708 13:37:03 20130708 13:47:03 900
20130708 13:47:03 20130708 14:07:03 0
20130708 14:07:03 20130708 14:17:03 900
20130708 14:17:03 20130708 14:27:03 900
20130708 14:27:03 20130708 14:37:03 900
20130708 14:37:03 20130708 14:47:03 900
20130708 14:47:03 20130708 14:57:03 900
BEGIN_TIME END_TIME TUNED_UNDORETENTION
----------------- ----------------- -------------------
20130708 14:57:03 20130708 15:07:03 900
20130708 15:07:03 20130708 15:17:03 900
20130708 15:17:03 20130708 15:27:03 900
20130708 15:27:03 20130708 15:37:03 900
20130708 15:37:03 20130708 15:47:03 900
20130708 15:47:03 20130708 15:57:03 900
20130708 15:57:03 20130708 16:05:51 900
29 rows selected.
SQL>
如配置undo_retention，则当前的undo retention值动态调整后不能小于undo_retention
但如启用retention guarantee，如空间不足，则当前的undo retention值动态调整后可能小于undo_retention
--undo_retention修改后直接生效，它以undo tablespace为基础，即使你配置过大，还是以undo tablespace size为准
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_retention=1000 scope=memory;
System altered.
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1000
undo_tablespace string UNDOTBS1
如何定义undo tablespace的大小
1，针对固定大小非自动扩展，采用oem或undo advisor即dbms_advisor来调节undo tablespace的大小
2，而对于自动扩展，由oracle自动调节其大小即可
undo advisor
1,它取决于awr收集信息是否充足
2,awr收集的间隔
为了使用undo advisor
1，首先配置2个值，最大查询所用的时间
2，最长闪回查询的时间
取上述最大，即为固定大小undo tablespace大小
undo advisor使用
1，具体用法参考：Oracle Database 2 Day DBA
2，示例：
DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.SET_TASK_PARAMETER(name, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(tname);
end;
/
运行结果可从oem或者从dba_advisor相关字典查询
创建undo tablespace的方法：
1，直接在建数据库通过create database构建undo tablespace
示例：
create database test
.
.
.
undo tablespace undotbs1 datafile '/oracle10g/undotbs1.dbf';
2,数据库已存在情况下，create undo tablespace亦可创建undo tablespace
示例：
create undo tablespace undotbs1 datafile '/oracle10g/undotbs1.dbf' size 2m reuse autoextend on;
变更undo tablespace的几种情况：
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 25 32767.9844 YES
SQL> !ls -lh /oracle10g/oradata/first/undotbs01.dbf
-rw-r----- 1 oracle10g oinstall 26M Jul 8 17:05 /oracle10g/oradata/first/undotbs01.dbf
*
-----启用undo tablespace自动扩展且最大值为50m
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' autoextend on maxsize 50m;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 25 50 YES
--启用undo tablespace自动扩展且最大值为unlimited
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' autoextend on maxsize unlimited;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 25 32767.9844 YES
SQL>
---重定义undo tablespace大小
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' resize 35m;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
--添加数据文件到undo tablespace
SQL> alter tablespace undotbs1 add datafile '/oracle10g/oradata/first/undotbs01_second.dbf' size 10m;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
UNDOTBS1 /oracle10g/oradata/first/undotbs01_second.dbf 10 0 NO
--从undo tablespace删除数据文件
SQL> alter tablespace undotbs1 drop datafile '/oracle10g/oradata/first/undotbs01_second.dbf';
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
SQL>
---直接离线undo tablespace不可以
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
---直接离线undo tablespace对应的数据文件可以
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' offline;
alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
－－－离线undo tablespace后alert大量报错如下
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle10g/oradata/first/undotbs01.dbf'
Mon Jul 8 17:20:44 2013
Errors in file /oracle10g/admin/first/bdump/first_smon_2589.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle10g/oradata/first/undotbs01.dbf'
Mon Jul 8 17:20:45 2013
alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online
Mon Jul 8 17:20:45 2013
Completed: alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online
---离线undo tablespace的数据文件后再次在线报错，需要介质恢复
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online;
alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oracle10g/oradata/first/undotbs01.dbf'
--介质恢复undo tablespace datafile 2
SQL> recover datafile 2;
ORA-00603: ORACLE server session terminated by fatal error
--在线undo tablespace datafile 2
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01.dbf' online;
Database altered.
--添加一个文件到undo tablespace
SQL> alter tablespace undotbs1 add datafile '/oracle10g/oradata/first/undotbs01_second.dbf' size 10m;
Tablespace altered.
--离线新增数据文件
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' offline;
Database altered.
--alert内容如下：
Mon Jul 8 17:27:00 2013
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' offline
Mon Jul 8 17:27:00 2013
Completed: alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' offline
---在线数据文件提示介质恢复
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online;
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle10g/oradata/first/undotbs01_second.dbf'
---alert内容如下：
Mon Jul 8 17:28:54 2013
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
Mon Jul 8 17:28:54 2013
ORA-1113 signalled during: alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online...
--介质恢复
SQL> recover datafile 5;
Media recovery complete.
--alert内容如下：
Mon Jul 8 17:30:25 2013
ALTER DATABASE RECOVER datafile 5
Mon Jul 8 17:30:25 2013
Media Recovery Start
Mon Jul 8 17:30:25 2013
Recovery of Online Redo Log: Thread 1 Group 1 Seq 35 Reading mem 0
Mem# 0 errs 0: /oracle10g/oradata/first/redo01.log
Mon Jul 8 17:30:25 2013
Media Recovery Complete (first)
Completed: ALTER DATABASE RECOVER datafile 5
--再次在线成功
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online;
Database altered.
---alert内容如下
Mon Jul 8 17:31:44 2013
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
Mon Jul 8 17:31:44 2013
Completed: alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' online
---undo tablespace的undo retention属性列guarantee
SQL> select tablespace_name,status,retention from dba_tablespaces where tablespace_name='UNDOTBS1';
TABLESPACE_NAME STATUS RETENTION
------------------------------ --------- -----------
UNDOTBS1 ONLINE NOGUARANTEE
---由noguarantee变更为guarantee
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
SQL> select tablespace_name,status,retention from dba_tablespaces where tablespace_name='UNDOTBS1';
TABLESPACE_NAME STATUS RETENTION
------------------------------ --------- -----------
UNDOTBS1 ONLINE GUARANTEE
---在sqlplus下不能热备一个undo tablespace的数据文件
SQL> alter database file '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter database file '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup;
alter database datafile '/oracle10g/oradata/first/undotbs01_second.dbf' begin backup
*
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected
---重合名undo tablespace datafile
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
UNDOTBS1 /oracle10g/oradata/first/undotbs01_second.dbf
--必须先复制一个重命名的数据文件到目标目录
SQL> alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf
2 ' to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf';
alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "/oracle10g/oradata/first/undotbs01_second.dbf
"
---复制一个重命名的数据文件到目标目录
SQL> !cp -p /oracle10g/oradata/first/undotbs01_second.dbf /oracle10g/oradata/first/undotbs01_secondnewly.dbf
SQL> alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf';
alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf'
*
ERROR at line 1:
ORA-00946: missing TO keyword
---rename成功
SQL> alter database rename file '/oracle10g/oradata/first/undotbs01_second.dbf' to '/oracle10g/oradata/first/undotbs01_secondnewly.dbf';
Database altered.
--查询rename成果
SQL> select tablespace_name,file_name,bytes/1024/1024 as curmb,maxbytes/1024/1024 as maxmb,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
TABLESPACE_NAME FILE_NAME CURMB MAXMB AUT
------------------------------ -------------------------------------------------- ---------- ---------- ---
UNDOTBS1 /oracle10g/oradata/first/undotbs01.dbf 35 32767.9844 YES
UNDOTBS1 /oracle10g/oradata/first/undotbs01_secondnewly.dbf
SQL>
---如undo tablespace还被实例使用，不能删除，如果有多个undo tablespace，删除其中之一时，确保没有包含unexpired undo data;
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
--新增一个undo tablespace
SQL> create undo tablespace undotbs2 datafile '/oracle10g/oradata/first/undo2.dbf' size 10m;
Tablespace created.
--新建的undo tablespace状态为offline,即未使用
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ OFFLINE
UNDOTBS2 _SYSSMU19$ OFFLINE
UNDOTBS2 _SYSSMU18$ OFFLINE
UNDOTBS2 _SYSSMU17$ OFFLINE
UNDOTBS2 _SYSSMU16$ OFFLINE
UNDOTBS2 _SYSSMU15$ OFFLINE
UNDOTBS2 _SYSSMU14$ OFFLINE
UNDOTBS2 _SYSSMU13$ OFFLINE
UNDOTBS2 _SYSSMU12$ OFFLINE
UNDOTBS2 _SYSSMU11$ OFFLINE
21 rows selected.
切换undo tablespace
1,此动作可以马上生效
示例：
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ ONLINE
UNDOTBS2 _SYSSMU19$ ONLINE
UNDOTBS2 _SYSSMU18$ ONLINE
UNDOTBS2 _SYSSMU17$ ONLINE
UNDOTBS2 _SYSSMU16$ ONLINE
UNDOTBS2 _SYSSMU15$ ONLINE
UNDOTBS2 _SYSSMU14$ ONLINE
UNDOTBS2 _SYSSMU13$ ONLINE
UNDOTBS2 _SYSSMU12$ ONLINE
UNDOTBS2 _SYSSMU11$ ONLINE
21 rows selected.
---变更默认undo tablespace之前
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ OFFLINE
UNDOTBS2 _SYSSMU19$ OFFLINE
UNDOTBS2 _SYSSMU18$ OFFLINE
UNDOTBS2 _SYSSMU17$ OFFLINE
UNDOTBS2 _SYSSMU16$ OFFLINE
UNDOTBS2 _SYSSMU15$ OFFLINE
UNDOTBS2 _SYSSMU14$ OFFLINE
UNDOTBS2 _SYSSMU13$ OFFLINE
UNDOTBS2 _SYSSMU12$ OFFLINE
UNDOTBS2 _SYSSMU11$ OFFLINE
21 rows selected.
--不提交产生一个dml
[oracle10g@rhel5 bdump]$ sqlplus scott/system@first
SQL> update dept set loc=loc;
4 rows updated.
---即便要切换的undo tablespace包含未提交事务，也可以切换到另一个undo tablespace
SQL> alter system set undo_tablespace=undotbs2;
System altered.
---删除切换之前的undo tablespace报错，因为它还包含未提交的事务
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
---如下查询也证实undotbs1的_SYSSMU8$段包含活动事务
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS2 _SYSSMU20$ ONLINE
UNDOTBS2 _SYSSMU19$ ONLINE
UNDOTBS2 _SYSSMU18$ ONLINE
UNDOTBS2 _SYSSMU17$ ONLINE
UNDOTBS2 _SYSSMU16$ ONLINE
UNDOTBS2 _SYSSMU15$ ONLINE
UNDOTBS2 _SYSSMU14$ ONLINE
UNDOTBS2 _SYSSMU13$ ONLINE
UNDOTBS2 _SYSSMU12$ ONLINE
UNDOTBS2 _SYSSMU11$ ONLINE
21 rows selected.
--查询当前在用的undo tablespace
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 100000
undo_tablespace string UNDOTBS1
--指定不存在的undo tablespace直接报错
SQL> alter system set undo_tablespace=undotbs8;
alter system set undo_tablespace=undotbs8
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'UNDOTBS8' does not exist or of wrong type
--把当前在用的重新指定使用也是ok的
SQL> alter system set undo_tablespace=undotbs1;
System altered.
---指定非undo tablespace直接报错
SQL> alter system set undo_tablespace=users;
alter system set undo_tablespace=users
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30012: undo tablespace 'USERS' does not exist or of wrong type
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
11 rows selected.
SQL>
---查看当前使用的undo tablespace
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
11 rows selected.
---以空格方式指定当前使用的undo tablespace
SQL> alter system set undo_tablespace='';
System altered.
---空格指定后undotbs1状态变为offline
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE
11 rows selected.
---空格指定后undotbs1也变为空
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 184550656 bytes
Database Buffers 348127232 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
--重启数据库后又恢复到之前的配置
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE
11 rows selected.
SQL>
迁移到自动undo管理模式
示例：
--返回大小相关信息
declare
x number;
begin
x:＝dbms_undo_adv.rbu_migration;
end;
与undo tablespace相关的字典
v$undostat 管理undo usage相关信息
dba_hist_undostat 为上述的历史信息
v$rollstat 包含undo segment相关信息
v$transaction 与undo segment相关的信息
dba_undo_extents 包含undo tablespace每个extent相关信息
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-765813/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
