ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g undo tablespace相关知识

oracle10g undo tablespace相关知识

原创 Linux操作系统 作者:wisdomone1 时间:2013-07-08 18:51:05 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-765813/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历: 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间:2008-04-04

  • 博文量
    2189
  • 访问量
    11974869