undo表空间维护

undo表空间爆满处理步骤：在插入数据或者update时，有时会报ora_30036错误，导致数据插入失败，其原因是数据库的undo表空间不足所致，解决undo表空间间满的方法有两种。
1 查看undo表空间使用情况：

show parameter undo
select status,sum(bytes/1024/1024) from dba_undo_extents where
tablespace_name='UNDOTBS1' group by status;

2 减小undo_retention的值，默认是900（15min）

3 向undo表空间增加数据文件

4 切换undo表空间，建立新的undo表空间
维护UNDO表空间

维护undo表空间包括：重命名、增加数据文件、数据文件设置offline或online等

一 更改undo_retention参数

查询当前参数的时间，直接查询可以，也可以通过v$parameter数据字典参数表直接查询

设置undo_retention为10800秒（3h），结合具体情况设置

alter system set undo_retention = 1200;

SQL> show parameter undo_retention

NAME TYPE VALUE

----------------- ------------- ------------

undo_retention integer 1200

二 UNDO表空间重命名

undo表空间重命名：使用rename命令，下面测试修改非系统UNDO表空间TEST_UNDO和系统UNDO表空间UNDOTBS1

修改非系统默认UNDO表空间

alter tablespace test_undo rename to test_undo_two;

非系统默认UNDO表空间修改后立即生效

SQL> alter tablespace undotbs1 rename to undo_num_one;

系统默认undo表空间修改后需要重启才能生效

SQL> shutdown immediate；

SQL> show parameter undo

NAME TYPE VALUE

----------------------------------- ----------- ------------------------------

undo_management string AUTO

undo_retention integer 1200

undo_tablespace string UNDO_NUM_ONE

三 向UNDO表空间添加数据文件

查询TEST_UNDO_TWO表空间大小

alter tablespace test_undo_two add datafile '/u01/app/oracle/oradata/dg1/test_undo_add1.dbf' size 100m;

四 切换当前默认UNDO表空间

在Oracle中一个实例只允许有一个active(活跃)的还原表空间，其他undo表空间全部为inactive（不活跃）的状态，RAC为所有实例的active状态undo表空间全部结合为一个共有还原段使用。

1、数据库状态静止时（无DML操作期间）执行UNDO表空间切换（由UNDOTBS1切换为UNDOTBS2）

（1）创建新的undo表空间UNDOTBS2

SQL> create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 10M;

Tablespace created.

（2）切换UNDOTBS2为新的undo表空间

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

System altered.

（3）此时数据库处于静止状态，无任何DML操作，查看UNDOTBS1已经处于OFFLINE状态

SQL>select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;

TABLESPACE_NAME                STATUS             COUNT(*)

----------------------------- ---------------- ----------

UNDOTBS1                       OFFLINE                  10

SYSTEM                             ONLINE                    1

UNDOTBS2                       ONLINE                   10

（4）检查确认UNDOTBS1中没有ONLINE的segment

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

no rows selected

（5）删除旧的UNDOTBS1

SQL>Drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

（6）至此，undo表空间由UNDOTBS1成功切换为UNDOTBS2.

2、数据库中有DML操作期间，切换UNDO表空间步骤（由UNDOTBS2切换为UNDOTBS1）

session 1正在执行如下操作：

conn test/test

create table test (name varchar(2));

insert into test values ('zhangsan');

commit;

update test set name='lisi' where name='zhangsan';

此时未提交

session 2开始切换undo表空间操作

（1）确认当前使用的undo表空间

SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE

----------------------------------- ----------- ------------------------------

undo_tablespace                      string      UNDOTBS2

（2）查看当前undo表空间的所有SELMENT均为ONLINE状态

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';

STATUS           SEGMENT_NAME

--------------- ------------------------------

ONLINE           _SYSSMU11_3423735372$

ONLINE           _SYSSMU12_567660877$

ONLINE           _SYSSMU13_100563780$

ONLINE           _SYSSMU14_1447748955$

ONLINE           _SYSSMU15_478708454$

ONLINE           _SYSSMU16_3309423900$

ONLINE           _SYSSMU17_525951688$

ONLINE           _SYSSMU18_130984470$

ONLINE           _SYSSMU19_3964826557$

ONLINE           _SYSSMU20_994913344$

10 rows selected.

查看实时undo使用量：

set linesize 220
set pagesize 1000
col username for a20
col module for a40
col sql_id for a15
col status for a10
col machine for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select *
from (select start_time,
username,
s.MACHINE,
s.OSUSER,
r.name,
ubafil, --Undo block address (UBA) filenum
ubablk, --UBA block number
t.status,
(used_ublk * 8192 / 1024) kbtye,
used_urec,
s1.SQL_ID,
substr(s1.SQL_TEXT,0,20)
from v$transaction t, v$rollname r, v$session s, v$sqlarea s1
where t.xidusn = r.usn
and s.saddr = t.ses_addr
and s.sql_id = s1.sql_id(+)
order by 9 desc)
where rownum <= 10;

查询表空间使用情况（精确到大小）

SELECT   a.tablespace_name,
ROUND (a.total_size) "total_size(MB)",
ROUND (a.total_size) - ROUND (b.free_size,3) "used_size(MB)",
ROUND (b.free_size, 3) "free_size(MB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM   (  SELECT   tablespace_name, SUM (bytes)/1024/1024 total_size
FROM   dba_data_files
GROUP BY   tablespace_name) a,
(  SELECT   tablespace_name, SUM (bytes)/1024/1024 free_size
FROM   dba_free_space
GROUP BY   tablespace_name) b
WHERE   a.tablespace_name = b.tablespace_name(+);

查询当前使用回滚段的语句

col username for a20
col osuser for a30
col segment_name for a30
select substr(username,1,10) username,sid,serial#,osuser,segment_name,used_ublk
from v$transaction,dba_rollback_segs,v$session
where SADDR=SES_ADDR and XIDUSN=SEGMENT_ID
order by used_ublk;