undo表空间自动扩展 为什么oracle_oracle undo表空间 - CSDN
精华内容
参与话题
  • undo表空间暴长,如何取消自动扩展

    千次阅读 2008-04-15 15:07:00
     俺说了一下自己的想法: 重新建立一个undo2表空间,不要设置为自动扩展,指定最大值,然后切换undo1到这个新的undo2表空间,等到所有事务都移动到undo2以后,删除undo1即可。<!--google_ad_client = "pub-6026
    今天在itpub上被问到一个问题,undo表空间原来被设置成了自动扩展,现在已经有10G大小了,如果这样下去,硬盘早晚撑暴;
     俺说了一下自己的想法:
      重新建立一个undo2表空间,不要设置为自动扩展,指定最大值,然后切换undo1到这个新的undo2表空间,等到所有事务都移动到undo2以后,删除undo1即可。

     如果原来的undo1上没有活动的事务的话,可以直接删除,重新建立。
     
    查了一下资料,用一下命令:
    alter database datafile 文件路径 autoextend off;取消自动扩展,
    alter database datafile 文件路径 autoextend on;设置自动扩展。
    也可以实现。
     
    查看表空间数据文件是否为自动扩展:
    SQL> col FILE_NAME format a40
    SQL> col TABLESPACE_NAME format a20
    SQL> select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id;
      
       FILE_ID FILE_NAME TABLESPACE_NAME AUT
      ---------- ---------------------------------------- -------------------- ---
       1 C:/ORACLE/ORADATA/ORADB/SYSTEM01.DBF SYSTEM YES
       2 C:/ORACLE/ORADATA/ORADB/RBS01.DBF RBS YES
       3 C:/ORACLE/ORADATA/ORADB/USERS01.DBF USERS YES
       4 C:/ORACLE/ORADATA/ORADB/TEMP01.DBF TEMP YES
       5 C:/ORACLE/ORADATA/ORADB/TOOLS01.DBF TOOLS YES
       6 C:/ORACLE/ORADATA/ORADB/INDX01.DBF INDX YES
       7 C:/ORACLE/ORADATA/ORADB/DR01.DBF DRSYS YES
       8 D:/TEST.DBF TEST NO
    已选择8行。
    说明:TEST表空间用以下语句创建的一个实验表空间,没有指定AUTOEXTEND ON 参数,所以不是自动扩展。
      SQL> create tablespace test datafile
       2 'd:/test.dbf' size 5M
       3 default storage (initial 1M next 1M pctincrease 0)
       4 /

     
    展开全文
  • oracle 释放过度使用的Undo表空间

    千次阅读 2007-04-29 12:13:00
     有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况;2. 有较大事务没有收缩或者没有提交所导制;说 明:本问题在ORACLE系统管理中属于比较正常的一现象,日常维护多注意对磁盘空间的监控。备 ...

    故障现象:UNDO表空间越来越大,长此下去最终数据因为磁盘空间不足而崩溃;

    问题分析:产生问题的原因主要以下两点:
    1. 有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况;
    2. 有较大事务没有收缩或者没有提交所导制;
    说    明:本问题在ORACLE系统管理中属于比较正常的一现象,日常维护多注意对磁盘空间的监控。
    备    份: (如果没有在线事务,可以不做,关闭监听)
     
    $>exp vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log owner=vas rows=y indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000M

    解决步骤:
    1. 启动SQLPLUS,并用sys登陆到数据库。

    #su - oracle
    $>sqlplus /nolog
    SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 8 13:45:10 2006
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    SQL> conn sys/qq994238@ddptest as sysdba;
    Connected.

    2. 查找数据库的UNDO表空间名

    #cat $ORACLE_HOME/dbs/initddptest.ora
    ……
    *.undo_management=’AUTO’
    *.undo_retention=10800
    *.undo_tablespace=’UNDOTBS2’
    ……

    3. 确认UNDO表空间;

    SQL> select name from v$tablespace;

    NAME
    ------------------------------
    CWMLITE
    DRSYS
    EXAMPLE
    INDX
    ODM
    SYSTEM
    TOOLS
    USERS
    XDB
    TEMP
    TESTLIB
    UNDOTBS2

    4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置;

    SQL>select file_name,bytes/1024/1024 from dba_data_files
      2  where tablespace_name like 'UNDOTBS2';
        
    5. 查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。

    SQL> select s.username, u.name from v$transaction t,v$rollstat r,
      2  v$rollname u,v$session s where s.taddr=t.addr and
      3  t.xidusn=r.usn and r.usn=u.usn order by s.username;

    6. 检查UNDO Segment状态;

    SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
      2  from v$rollstat order by rssize;


    7. 创建新的UNDO表空间,并设置自动扩展参数;

    SQL> create undo tablespace undotbs1 datafile '/oradata/oradata/ddptest/UNDOTBS1.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;

    Tablespace created.

    8. 动态更改spfile配置文件;

    SQL> alter system set undo_tablespace=undotbs1 scope=both;

    System altered.

    9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE;

    SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
      2 from v$rollstat order by rssize;

    10. 再执行看UNDO表空间所有UNDO SEGMENT ONLINE;

    SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
      2 from v$rollstat order by rssize;

    11. 删除原有的UNDO表空间;

    SQL> drop tablespace undotbs2 including contents;

    Tablespace dropped.

    12. 确认删除是否成功;

    SQL> select name from v$tablespace;

    NAME
    ------------------------------
    CWMLITE
    DRSYS
    EXAMPLE
    INDX
    ODM
    SYSTEM
    TOOLS
    USERS
    XDB
    TEMP
    TESTLIB
    UNDOTBS1

    12 rows selected.

    13. 在做此步骤前,请到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下内容是否发生变更:

    #cat $ORACLE_HOME/dbs/initddptest.ora
    ……
    *.undo_management=’AUTO’
    *.undo_retention=10800
    *.undo_tablespace=’UNDOTBS2’
    ……

     如果没有发生变更请执行如下语句:

    SQL> create pfile from spfile;

    File created.

    14. 册除原UNDO表空间的数据文件,其文件名为步骤中执行的结果。

    #rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf

     

    附:UNDO表空间介绍

    UNDO表空间用于存放UNDO数据,当执行DML操作(INSERT,UPDATEDELETE),oracle会将这些操作的旧数据写入到UNDO,oracle9i之前,管理UNDO数据时使用(Rollback Segment)完成的.oracle9i开始,管理UNDO数据不仅可以使用回滚段,还可以使用UNDO表空间.因为规划和管理回滚段比较复杂,所有oracle database 10g已经完全丢弃用回滚段.并且使用UNDO表空间来管理UNDO数据.

    UNDO数据也称为回滚(ROLLBACK)数据,它用于确保数据的一致性.当执行DML操作时,事务操作前的数据被称为UNDO记录.UNDO段用于保存事务所修改数据的旧值,其中存储着被修改数据块的位置以及修改前数据,

    UNDO数据的作用.

    1,回退事务

    当执行DML操作修改数据时,UNDO数据被存放到UNDO,而新数据则被存放到数据段中,如果事务操作存在问题,旧需要回退事务,以取消事务变化.假定用户A执行了语句UPDATE emp SET sal=1000 WHERE empno=7788后发现,应该修改雇员7963的工资,而不是雇员7788的工资,那么通过执行ROLLBACK语句可以取消事务变化.当执行ROLLBACK命令时,oracle会将UNDO段的UNDO数据800写回的数据段中.

    2,读一致性

    用户检索数据库数据时,oracle总是使用用户只能看到被提交过的数据(读取提交)或特定时间点的数据(SELECT语句时间点).这样可以确保数据的一致性.例如,当用户A执行语句UPDATE emp SET sal=1000 WHERE empno=7788,UNDO记录会被存放到回滚段中,而新数据则会存放到EMP段中;假定此时该数据尚未提交,并且用户B执行SELECT sal FROM emp WHERE empno=7788,此时用户B将取得UNDO数据800,而该数据正是在UNDO记录中取得的.

    3,事务恢复

    事务恢复是例程恢复的一部分,它是由oracle server自动完成的.如果在数据库运行过程中出现例程失败(如断电,内存故障,后台进程故障等),那么当重启oracle server,后台进程SMON会自动执行例程恢复,执行例程恢复时,oracl会重新做所有未应用的记录.回退未提交事务.

    4,倒叙查询(FlashBack Query)

    倒叙查询用于取得特定时间点的数据库数据,它是9i新增加的特性,假定当前时间为上午11:00,某用户在上午10:00执行UPDATE emp SET sal=3500 WHERE empno=7788语句,修改并提交了事务(雇员原工资为3000),为了取得10:00之前的雇员工资,用户可以使用倒叙查询特征.

    使用UNDO参数

    1,UNDO_MANAGEMENT

    该初始化参数用于指定UNDO数据的管理方式.如果要使用自动管理模式,必须设置该参数为AUTO,如果使用手工管理模式,必须设置该参数为MANUAL,使用自动管理模式时,oracle会使用undo表空间管理undo管理,使用手工管理模式时,oracle会使用回滚段管理undo数据,

    需要注意,使用自动管理模式时,如果没有配置初始化参数UNDO_TABLESPACE,oracle会自动选择第一个可用的UNDO表空间存放UNDO数据,如果没有可用的UNDO表空间,oracle会使用SYSTEM回滚段存放UNDO记录,并在ALTER文件中记载警告.

    2,UNDO_TABLESPACE

    该初始化参数用于指定例程所要使用的UNDO表空间,使用自动UNDO管理模式时,通过配置该参数可以指定例程所要使用的UNDO表空间.

    RAC(Real Application Cluster)结构中,因为一个UNDO表空间不能由多个例程同时使用,所有必须为每个例程配置一个独立的UNDO表空间.

    3,UNDO_RETENTION

    该初始化参数用于控制UNDO数据的最大保留时间,其默认值为900,9i开始,通过配置该初始化参数,可以指定undo数据的保留时间,从而确定倒叙查询特征(Flashback Query)可以查看到的最早时间点.

    建立UNDO表空间,

    UNDO表空间专门用于存放UNDO数据,并且在UNDO表空间尚不能建立任何数据对象(,索引,)

    1,使用CREATE DATABASE命令建立UNDO表空间.

    当使用CREATE DATABASE命令建立数据库时,通过指定UNDO TABLESPACE选项,可以建立UNDO表空间.示例如下:

    CREATE DATABASE db01

    UNDO TABLESPACE undotbs_01

    DATAFILE ‘/u01/oracle/rbdb1/undo0101.dbf’ SIZE 30M;

    注意:UNDO TABLESPACE 子句不是必须的,如果使用自动UNDO管理模式,并且没有指定该子句,那么建立数据库时会自动生成名为SYS_UNDOTBSUNDO表空间.

    2,使用CREATE UNDO TABLESPACE命令建立UNDO表空间.

    CREATE UNDO TABLESPACE undotbs3

    DATAFILE ‘D:demoundotbs3.dbf’ SIZE 10M;

    修改UNDO表空间,

    使用ALTER TABLESPACE命令修改UNDO表空间.

    当事务用尽了UNDO表空间后,使用ALTER TABLESPACE … ADD DATAFILE增加数据文件

    UNDO表空间所在的磁盘填满是,使用ALTER TABLESPACE … RENAME DATAFIEL 命令移动数据文件到其他磁盘上.

    使用ALTER DATABASE … OFFLINE/ONLINE使表空间脱机/联机.

    当数据库处于ARCHIVELOG模式时,使用ALTER TABLESPACE …BEGIN BACKUP/END BACKUP命令备份UNDO表空间.

    切换UNDO表空间.

    启动例程并打开数据库后,同一时刻特定例程只能使用一个UNDO表空间,切换UNDO表空间是指停止例程当前使用的UNDO表空间,并启动其他UNDO表空间,下面以启用undotbs2表空间为例,说明切换UNDO表空间的方法.

    ALTER SYSTEM SET undo_tablespace=undotbs02;

    RAC(Real Application Cluster)机构中,不同例程必须使用独立的UNDO表空间,而不能共用同一个UNDO表空间.

    删除UNDO表空间.

    当前例程正在使用的UNDO表空间是不能被删除的,如果确定要删除当前例程正在使用的UNDO表空间,应首先切换UNDO表空间.然后删除相应的UNDO表空间.

    DROP TABLESPACE undotbs3;

    1,确定当前例程正在使用的UNDO表空间.

    Show parameter undo_tablespace

    2,显示数据库的所有UNDO表空间.

    SELECT tablespace_name FROMdba_tablespaces WHERE contents=’UNDO’;

    3,显示UNDO表空间统计信息.

    使用自动UNDO管理模式时,需要合理地设置UNDO表空间的尺寸,为例合理规划UNDO表空间尺寸,应在数据库运行的高峰阶段搜集UNDO表空间的统计信息.最终根据该统计信息确定UNDO表空间的尺寸.通过查询动态性能视图V%UNDOSTAT,可以搜集UNDO统计信息.

    SELECT TO_CHAR(BEGIN_TIME,’HH24:MI:SS’) BEGIN_TIME,

    TO_CHAR(END_TIME,’HH24:MI:SS’) END_TIME,

    UNDOBLKS

    FROM V$UNDOSTAT;

    BEGIN_TIME用于标识起始统计时间,END_TIME用于标识结束统计时间,UNDOBLKS用于标识UNDO数据所占用的数据块个数.oracle每隔10分钟生成一行统计信息.

    4,显示UNDO段统计信息.

    使用自动UNDO管理模式时,oracle会在UNDO表空间上自动建立10UNDO,通过查询动态信息视图V$ROLLNAME,可以显示所有联机UNDO段的名称,通过查询动态性能视图V$ROLLLISTAT,可以显示UNDO段的统计信息.通过在V$ROLLNAMEV$ROLLLISTAT之间执行连接查询,可以监视特定UNDO段的特定信息.

    SELECT a.name, b.xacts, b.writes, b.extents

    FROM v$rollname a, v$rollstat b

    WHERE a.usn=b.usn;

    Name用于标识UNDO段的名称,xacts用于标识UNDO段所包含的活动事务个数,

    Writes用于标识在undo段上所写入的字节数,extents用于标识UNDO段的区个数.

    5,显示活动事务信息.

    当执行DML操作时,oracle会将这些操作的旧数据放到UNDO段中,动态性能视图v$session用于显示会话的详细信息,动态性能视图v$transaction用于显示事务的详细信息,动态性能视图v$rollname用于显示联机UNDO段的名称.通过在这3个动态性能视图之间执行连接查询,可以确定正在执行事务操作的会话,事务所使用的UNDO,以及事务所占用的UNDO块个数.

    Col username format a10

    Col name format a10

    SELECT a.username, b.name, c.used_ublk

    FROM v$session a, v$rollname b, v$transaction c

    WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn

    AND a.username=’SCOTT’;

    6,显示UNDO区信息

    数据字典视图dba_undo_extents用于显示UNDO表空间所有区的详细信息.包括UNDO区尺寸和状态等信息.

    SELECT extend_id, bytes, status FROM dba_undo_extents

    WHERE segment_name’_SYSSMU5$’;

    其中,extent_id用于标识区编号,bytes用于标识区尺寸,status用于标识区状态(ACTIVE:表示该区处于活动状态,EXPIRED:标识该区未用).

     
    展开全文
  • Oracle undo表空间管理

    千次阅读 2019-12-26 15:53:04
    当在做DML操作的时候,在修改buffer cache里面的数据块的时候,会对当前块的旧镜像信息做一个保存,保存的数据块就是undo块。undo块上面保存着数据修改前的信息。 生成undo块的几个作用: 当事务没有提交,undo...

    当在做DML操作的时候,在修改buffer cache里面的数据块的时候,会对当前块的旧镜像信息做一个保存,保存的数据块就是undo块。undo块上面保存着数据修改前的信息。

     

    生成undo块的几个作用:

    1. 当事务没有提交,undo用来回滚事务
    2. 读一致性
    3. 在做实例恢复的时候对事务进行回滚
    4. Flashback query 闪回查询

    在9I之前undo块存储的方式是手工的方式进行存储,这个需要DBA根据事务的情况建立相应的回滚段然后手工改的方式为每一个事务分配回滚段。

    现在基本对undo块的存储通过自动,这个就使用了undo表空间。Oracle 9I以后对undo管理就不使用手工方式,因为随着事务增加,手动管理的方式容易在回滚段上面产生冲突。自动管理在undo空间online的时候可以自动的建立undo段,这一切都是oracle自动管理的,对dba很简单,只需要建立相应的undo表空间即可,让oracle自动管理。

     

    SQL> show parameter undo;
    
    
    
    NAME      TYPE  VALUE
    
    ------------------------------------ ----------- ------------------------------
    
    undo_management      string  AUTO
    
    undo_retention      integer  900
    
    undo_tablespace      string  UNDOTBS2

     

    undo_management采用的是自动管理AUTO,在9I之前都是manual手动管理。通过undo_tablespace指定undo表空间UNDOTBS2。对于undo的自动管理还有一个参数undo_retention(当在undo块上面所对应的事务在完成提交以后,这个undo块空间是可以再被循环使用的,为了使得undo的信息可以保存更长一点,因为这个对flashback query等都比较重要,尽量保留时间长一点,所以可以设置undo_retention这个参数。当提交事务以后,undo块可以保留多长时间不被覆盖),这个时间到底设置多长呢?一般建议超过数据库里面最长事务查询的时间,比如一个查询大概需要半小时,那么undo_rentention设置为半小时1800s,这样保证在做查询的时候不会出现ORA-0155快照太旧。

     

    在oracle数据库里面一般可以有多个undo表空间,但是只有一个undo表空间是active状态。

    SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces;
    
    
    
    TABLESPACE_NAME        STATUS  CONTENTS
    
    ------------------------------ --------- ---------
    
    SYSTEM        ONLINE  PERMANENT
    
    SYSAUX        ONLINE  PERMANENT
    
    TDS           ONLINE  PERMANENT
    
    UNDOTBS2      ONLINE  UNDO
    
    USER_TEMP     ONLINE  TEMPORARY
    
    
    
    SQL> create undo tablespace undotbs3
    
      2  datafile '/u01/app/oracle/oradata/oradb/undotbs03.dbf' size 100m
    
      3  autoextend on;
    
    
    
    Tablespace created.
    
    
    
    SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces;  --可以看到有两个undo表空间
    
    
    
    TABLESPACE_NAME        STATUS  CONTENTS
    
    ------------------------------ --------- ---------
    
    SYSTEM        ONLINE  PERMANENT
    
    SYSAUX        ONLINE  PERMANENT
    
    TDS           ONLINE  PERMANENT
    
    UNDOTBS2      ONLINE  UNDO
    
    USER_TEMP     ONLINE  TEMPORARY
    
    UNDOTBS3      ONLINE  UNDO
    
    
    
    6 rows selected.
    
    
    
    SQL> col SEGMENT_NAME for a30;
    
    SQL> set linesize 1600;
    
    SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from user_segments
    
      2  where tablespace_name='UNDOTBS3';
    
    
    
    SEGMENT_NAME        SEGMENT_TYPE   TABLESPACE_NAME
    
    ------------------------------ ------------------ ------------------------------
    
    _SYSSMU1_2546521612$        TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU2_347642666$            TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU3_3866846032$        TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU4_970427828$            TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU5_2070521138$        TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU6_312287002$            TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU7_3177245340$        TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU8_3841002300$        TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU9_1958399569$        TYPE2 UNDO   UNDOTBS3
    
    _SYSSMU10_994732307$        TYPE2 UNDO   UNDOTBS3
    
    
    
    10 rows selected.

    当undo表空间online的时候,oracle会自动的分配undo段,比如上面就分配了10个undo段。

     

    下面语句是查看undo段的状态,比如数据库里面有两个undo表空间,只有一个undo表空间是active状态,另外一个undo表空间不被使用,可以通过下面语句查看段的状态,通过dba_rollback_segs这个视图。

    SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS2';
    
    SEGMENT_NAME		       TABLESPACE_NAME	    STATUS
    ------------------------------ -------------------- ----------------
    _SYSSMU11_4202450068$	       UNDOTBS2 	    OFFLINE
    _SYSSMU12_2784461198$	       UNDOTBS2 	    OFFLINE
    _SYSSMU13_306909427$	       UNDOTBS2 	    OFFLINE
    _SYSSMU14_1288844830$	       UNDOTBS2 	    OFFLINE
    _SYSSMU15_1412425860$	       UNDOTBS2 	    OFFLINE
    _SYSSMU16_2466010722$	       UNDOTBS2 	    OFFLINE
    _SYSSMU17_2893287284$	       UNDOTBS2 	    OFFLINE
    _SYSSMU18_2611002218$	       UNDOTBS2 	    OFFLINE
    _SYSSMU19_1129654697$	       UNDOTBS2 	    OFFLINE
    _SYSSMU20_3826278671$	       UNDOTBS2 	    OFFLINE
    
    10 rows selected.
    
    SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS3';
    
    SEGMENT_NAME		       TABLESPACE_NAME	    STATUS
    ------------------------------ -------------------- ----------------
    _SYSSMU1_2546521612$	       UNDOTBS3 	    ONLINE
    _SYSSMU2_347642666$	           UNDOTBS3 	    ONLINE
    _SYSSMU3_3866846032$	       UNDOTBS3 	    ONLINE
    _SYSSMU4_970427828$	           UNDOTBS3 	    ONLINE
    _SYSSMU5_2070521138$	       UNDOTBS3 	    ONLINE
    _SYSSMU6_312287002$	           UNDOTBS3 	    ONLINE
    _SYSSMU7_3177245340$	       UNDOTBS3 	    ONLINE
    _SYSSMU8_3841002300$	       UNDOTBS3 	    ONLINE
    _SYSSMU9_1958399569$	       UNDOTBS3 	    ONLINE
    _SYSSMU10_994732307$	       UNDOTBS3 	    ONLINE
    
    10 rows selected.

     

    也可以通过两个动态视图v$rollstat,v$rollname去了解undo段的使用情况。

    SQL> select a.usn,a.name,b.XACTS,b.status from v$rollname a,v$rollstat b
    
      2  where a.usn=b.usn;
    
    
    
           USN NAME        XACTS STATUS
    
    ---------- ------------------------------ ---------- ---------------
    
     0 SYSTEM    0 ONLINE
    
    11 _SYSSMU11_4202450068$    0 ONLINE
    
    12 _SYSSMU12_2784461198$    0 ONLINE
    
    13 _SYSSMU13_306909427$    0 ONLINE
    
    14 _SYSSMU14_1288844830$    0 ONLINE
    
    15 _SYSSMU15_1412425860$    0 ONLINE
    
    16 _SYSSMU16_2466010722$    0 ONLINE
    
    17 _SYSSMU17_2893287284$    0 ONLINE
    
    18 _SYSSMU18_2611002218$    0 ONLINE
    
    19 _SYSSMU19_1129654697$    0 ONLINE
    
    20 _SYSSMU20_3826278671$    0 ONLINE
    
    
    
    11 rows selected.

    System回滚段是建立正在system表空间上面的,只能用于sys用户的事务操作,对于普通用户只能使用11-20回滚段

    这里使用Scott用户去产生一个事务
    
    SQL> insert into t values(1);
    
    
    
    1 row created.
    
    未提交然后再去查询
    
    SQL> /
    
    
    
           USN NAME        XACTS STATUS
    
    ---------- ------------------------------ ---------- ---------------
    
     0 SYSTEM    0 ONLINE
    
    11 _SYSSMU11_4202450068$    0 ONLINE
    
    12 _SYSSMU12_2784461198$    0 ONLINE
    
    13 _SYSSMU13_306909427$    0 ONLINE
    
    14 _SYSSMU14_1288844830$    0 ONLINE
    
    15 _SYSSMU15_1412425860$    0 ONLINE
    
    16 _SYSSMU16_2466010722$    0 ONLINE
    
    17 _SYSSMU17_2893287284$    0 ONLINE
    
    18 _SYSSMU18_2611002218$    0 ONLINE
    
    19 _SYSSMU19_1129654697$    0 ONLINE
    
    20 _SYSSMU20_3826278671$    1 ONLINE
    
    
    
    11 rows selected.

    可以看到红色部分的undo段,XACTS,X代表事务,ACTS代表活跃的事务,20回滚段有未提交的事务,对应的undo信息保存在这个回滚段上面,一旦提交就为0了,表示undo对应的信息就没了,空间就可以再次被使用。

     

    对于undo表空间一般会打开自动扩展,随着事务增多undo表空间的数据文件会越来越大,这样会占用大量磁盘空间,这个空间只会不断扩大,不会回收。

    为了解决undo表空间过大,可以考虑新建立一个undo表空间,对undo表空间进行一个切换,然后将之前大的undo表空间进行删除释放空间,注意不能立刻将之前的undo表空间删除,因为切换以后可能在原有的undo表空间上面记录的一些事务还没有完成,如果将这个undo表空间给删除了,库意外宕机重启了,在做实例恢复回滚的时候就找不到undo数据了,库就可能打不开了。

    在数据库正常关闭再启动或者保证原有undo表空间事务都完成了提交了再去删除原来的undo表空间。

    SQL> show parameter undo;
    
    
    
    NAME      TYPE  VALUE
    
    ------------------------------------ ----------- ------------------------------
    
    undo_management      string  AUTO
    
    undo_retention      integer  900
    
    undo_tablespace      string  UNDOTBS2
    
    SQL> alter system set undo_tablespace='UNDOTBS3';
    
    
    
    System altered.
    
    
    
    SQL> show parameter undo;  
    
    
    
    NAME      TYPE  VALUE
    
    ------------------------------------ ----------- ------------------------------
    
    undo_management      string  AUTO
    
    undo_retention      integer  900
    
    undo_tablespace      string  UNDOTBS3
    
    

     

     

     

    展开全文
  • 我这边生产环境,一般undo表空间都会预先设置一个大小,并且保留数据文件的自动扩展,一直也相安无事。最近有套系统的undo表空间一直报警,使用率超过85%。看了下表空间大小,才5个G,以后确实是undo表空间过小了,...

          我这边生产环境,一般undo表空间都会预先设置一个大小,并且保留数据文件的自动扩展,一直也相安无事。最近有套系统的undo表空间一直报警,使用率超过85%。看了下表空间大小,才5个G,以后确实是undo表空间过小了,于是扩到10G。结果第二天又报警了,嘿,不信了,于是扩到20g。哎呦,跟我杠上了,第二天又报警了。

          没办法,生了个awr报告,看到到底是哪个倒霉孩子写的大事务一句一直不提交。哎呦我去,没有找到嫌疑语句,啥情况。找来开发人员,交流一下,不存在批量之类的业务。怪了。

         查了下undo状态:select status,sum(bytes)/1024/1024/1024 from dba_undo_extents group by status;发现未过期的undo竟然占了表空间的百分之九十左右。但是我实时查看数据库状态,确实没有发现正在活动的大事务。于是怀疑,莫非是遇到undo方面的bug了,是不是存在未过期undo一直没释放的情况?

        在MOS上看到一篇文档doc:413732.1,有如下解释:

    When the UNDO tablespace is created with NO AUTOEXTEND, following the allocation algorithm, here is the explanation for this correct behavior:

    For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with 10.2, we provide max retention given the fixed undo space, which is set to a value based on the UNDO tablespace size. 
    This means that even if the undo_retention is set to a number of seconds (900 default), the fixed UNDO tablespace supports a bigger undo_retention time interval (e.g: 36 hours), based on the tablespace size, thing that makes the undo extents to be UNEXPIRED. But this doesn't indicate that there are no available undo extents when a transaction will be run in the database, as the UNEXPIRED undo segments will be reused.

    大致意思是如果数据文件设置成非自动扩展的,oracle不会按照undo_retention参数所设置的值,对undo数据进行过期。我测试了下,在12.2.0.1版本,依然存在这样的情况。将上述存在问题的undo表空间数据文件设置成自动扩展后,过了一会,未过期的undo数据逐渐减少,直至undo表空间使用率恢复正常。

      oracle当真是博大精深,总有一些知识点很难触及,遇到了,总要记录一番。

    展开全文
  • Oracle 11g 扩展UNDO表空间

    千次阅读 2018-04-15 23:42:25
    Oracle 11g 扩展UNDO表空间1.查看数据文件位置set linesize 200col file_name for a50col tablespace_name for a20select file_id,file_name,tablespace_name,sum(bytes)/1024/1024 total_mb,autoextensible from ...
  • Oracle-UNDO表空间解读

    千次阅读 2020-07-31 15:18:31
    UNDO 表空间用于存放UNDO数据,当执行DML操作(INSERT,UPDATE和DELETE)时,oracle会将这些操作的旧数据写入到UNDO段。 在 oracle9i之前,管理UNDO数据时使用(Rollback Segment)完成的.从oracle9i开始,管理UNDO数据不仅...
  • Oracle快速替换UNDO表空间方法

    千次阅读 2017-10-21 15:56:37
    Oracle快速替换UNDO表空间方法 含快速替换脚本。
  • ORACLE UNDO表空间回收处理

    千次阅读 2017-09-14 17:19:14
    目前有一个10g的数据库在检查时发现undo表空间异常庞大,undo表空间占了将近45G 1、首先查询当前undo表空间的使用情况,有文件使用已经达到32G SQL> select file_name,bytes/1024/1024 MB,autoextensible from dba_...
  • Oracle 释放过度使用的Undo表空间

    万次阅读 2014-03-31 12:09:32
    1. 有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况; 2. 有较大事务没有收缩或者没有提交所导制; 说 明:本问题在ORACLE系统管理中属于比较正常的一现象,日常维护多注意对磁盘空间的监控。 ...
  • Oracle查看表空间是否有自动扩展

    千次阅读 2018-09-26 20:31:34
    系统管理员或普通用户登入PL/SQL develper 执行 select t.tablespace_name, d.file_name, d.autoextensible, d.bytes,d.maxbytes, d.status from dba_tablespaces t,dba_data_files d where t.tablespace_name =d....
  • ORACLEundo表空间操作

    2019-04-04 14:34:19
    1.查看undo表空间 selectfile_id,file_name,tablespace_name,sum(bytes)/1024/1024total_mb,autoextensible fromdba_data_filesgroupbyfile_name,file_id,tablespace_name,autoextensibleorderbyfile_id; ...
  • 运行正常的系统在批量更新数据时出现如下错误:ORA-30036: 无法按 8 扩展段 (在撤消表空间 UNDO 中)解决方法1:支持使用自动扩展使用DBA用户登录数据库后执行SQL语句:ALTER TABLESPACE Undo RETENTION GUARANTEE解决...
  • oracle undo表空间的清理

    万次阅读 2015-01-09 23:53:49
    找出UNDO表空间的路径及大小 SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1'; 检查UNDO Segment状态 SQL> select usn,xacts,rssize/1024/1024/1024,...
  • 最近发现实时数据库负载很高,数据库日志很多等待事件,检查IO正常,SQL查询也...要确定Oracle需要的UNDO 表空间的大小,需要以下三条信息:  UR 以秒单位的UNDO_RETENTION UPS 每秒生成的还原数据块的数量 DBS
  • Oracle UNDO表空间

    2017-03-20 11:55:16
    UNDO表空间
  • Oracle undo表空间爆满的处理方法

    千次阅读 2014-05-04 09:49:01
    Oracle undo表空间爆满的处理方法    Oracle undo表空间爆满的解决步骤:    1. 启动SQLPLUS,并用sys登陆到数据库。  #su - oracle  $>sqlplus / as sysdba    2. 查找数据库的UNDO表空间...
  • UNDOOracle中的一个很重要的机制,在对数据库进行修改的时候,Oracle会将数据块上修改之前的数据(称为前映像,before image)保存在回滚段中,这样当我们需要进行回滚(rollback)的时候就很容易能从回滚段中将之前...
  • 收缩undo表空间

    千次阅读 2013-10-27 21:08:30
    通常情况下,如果undo表空间的处于自动扩展且未指定最大值的情形,对于使用小表空间模式的数据库,undo表空间可能会一再增长,直到达到32GB。或者是在指定了自动扩展及其最大值而月底或年末的批量数据计算导致undo表...
1 2 3 4 5 ... 20
收藏数 6,753
精华内容 2,701
关键字:

undo表空间自动扩展 为什么oracle