SQL> col SEGMENT_NAME for a25
SQL> col TABLESPACE_NAME for a15
SQL> set pagesize 1000
SQL> select segment_name,max_extents,tablespace_name,status from dba_rollback_Segs;
SEGMENT_NAME MAX_EXTENTS TABLESPACE_NAME STATUS
------------------------- ----------- --------------- ----------------
SYSTEM 32765 SYSTEM ONLINE
_SYSSMU22_494124415$ 32765 UNDOTBS1 OFFLINE
_SYSSMU21_4101639344$ 32765 UNDOTBS1 OFFLINE
_SYSSMU10_1197734989$ 32765 UNDOTBS1 ONLINE
......
_SYSSMU13_3723745422$ 32765 UNDOTBS2 ONLINE
_SYSSMU12_1501422084$ 32765 UNDOTBS2 ONLINE
_SYSSMU11_2561412919$ 32765 UNDOTBS2 ONLINE
SQL> SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)" FROM dba_undo_extents GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
--------------- --------- ----------
UNDOTBS1 UNEXPIRED 18.1875
UNDOTBS2 UNEXPIRED 7.8125
UNDOTBS1 EXPIRED 42.1875
UNDOTBS2 EXPIRED 2.5
在自动回滚段管理中,回滚段的name无法限制,size也不能使用uniform,所以会造成rollback_segment的大小不一
SQL> select bytes/1024,count(*) from dba_extents where segment_name ='_SYSSMU12_1501422084$' group by bytes order by 1;
BYTES/1024 COUNT(*)
---------- ----------
64 2
1024 1
在数据库中有大事务的时候会报两种undo的错
1)、undo撑爆了,size级别
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
此时的处理相对简单,重建undo即可
1. 启动SQLPLUS,并用sys登陆到数据库。
#su - oracle
$>sqlplus / as sysdba
2. 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间:
SQL> show parameter undo_
3. 确认UNDO表空间;
SQL> select name from v$tablespace;
NAME
------------------------------
UNDOTBS1
4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置;
select file_name, bytes/1024/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';
5. 创建新的UNDO表空间,并设置自动扩展参数;
create undo tablespace undotbs2 datafile '/u01/oradata/undotbs2_01.dbf' size 30G reuse autoextend on next 100m maxsize 64G;
6. 切换UNDO表空间为新的UNDO表空间 , 动态更改spfile配置文件;
alter system set undo_tablespace=undotbs2 scope=both;
7.验证当前数据库的 UNDO表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
8. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE;
select t.segment_name, t.tablespace_name, t.segment_id, t.status
from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
_SYSSMU1$ UNDOTBS1 1 OFFLINE
_SYSSMU2$ UNDOTBS1 2 OFFLINE
......
_SYSSMU33$ UNDOTBS1 33 OFFLINE
_SYSSMU34$ UNDOTBS1 34 OFFLINE
_SYSSMU35$ UNDOTBS1 35 OFFLINE
上面对应的UNDOTBS1还原表空间所对应的回滚段均为OFFLINE
9.到$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.
10. 删除原有的UNDO表空间;
查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。
select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;
查询结果为空的话就能删除。
检查UNDO Segment状态;
select usn,
xacts,
rssize / 1024 / 1024 / 1024,
hwmsize / 1024 / 1024 / 1024,
shrinks
from v$rollstat
order by rssize;
若undo表空间中还存在回滚的对象,说明有session正在使用。
有可能遇到30013错误!
drop tablespace undotbs1 including contents and datafiles;
11. os级别释放undo数据文件;
到root下执行
lsof |grep /u01/oradata/undotbs01.dbf
lsof |grep /u01/oradata/undotbs01.dbf |awk '{printf"kill -9 "$2"\n"}'
2)、undo撑爆了,count级别
[oracle@ecardtestdb1 ~]$ oerr ora 01628
01628, 00000, "max # extents (%s) reached for rollback segment %s"
// *Cause: An attempt was made to extend a rollback segment that was
// already at the MAXEXTENTS value.
// *Action: If the value of the MAXEXTENTS storage parameter is less than
// the maximum allowed by the system, raise this value.
当有大事务时,rollback segment中的小extent 非常容易达到回滚段的上限,而导致无法拓展。
如果是第二种情况,则更加的简单
1、尝试另开会话,重新执行,主要看选择了哪一个回滚段,并且给回滚段在max_extents内可以完成该事物,则成功;否则还是会报相同的错误
2、批量提交(使用了游标的count属性),避免大sql的出现
DECLARE
CURSOR cur_fp_commit IS
SELECT * FROM t1;
TYPE recd IS TABLE OF t1%ROWTYPE;
recs recd;
BEGIN
OPEN cur_fp_commit;
WHILE (TRUE) LOOP
FETCH cur_fp_commit BULK COLLECT
INTO recs LIMIT 100;
FORALL i IN 1 .. recs.COUNT
INSERT INTO test VALUES recs (i);
COMMIT;
EXIT WHEN cur_fp_commit%NOTFOUND;
END LOOP;
CLOSE cur_fp_commit;
END;
/
3、shrink区数量比较多的回滚段
alter rollback segment "_SYSSMU24$" shrink;
4、重建undotablespace 与上述步骤一致
SQL> col TABLESPACE_NAME for a15
SQL> set pagesize 1000
SQL> select segment_name,max_extents,tablespace_name,status from dba_rollback_Segs;
SEGMENT_NAME MAX_EXTENTS TABLESPACE_NAME STATUS
------------------------- ----------- --------------- ----------------
SYSTEM 32765 SYSTEM ONLINE
_SYSSMU22_494124415$ 32765 UNDOTBS1 OFFLINE
_SYSSMU21_4101639344$ 32765 UNDOTBS1 OFFLINE
_SYSSMU10_1197734989$ 32765 UNDOTBS1 ONLINE
......
_SYSSMU13_3723745422$ 32765 UNDOTBS2 ONLINE
_SYSSMU12_1501422084$ 32765 UNDOTBS2 ONLINE
_SYSSMU11_2561412919$ 32765 UNDOTBS2 ONLINE
SQL> SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)" FROM dba_undo_extents GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
--------------- --------- ----------
UNDOTBS1 UNEXPIRED 18.1875
UNDOTBS2 UNEXPIRED 7.8125
UNDOTBS1 EXPIRED 42.1875
UNDOTBS2 EXPIRED 2.5
在自动回滚段管理中,回滚段的name无法限制,size也不能使用uniform,所以会造成rollback_segment的大小不一
SQL> select bytes/1024,count(*) from dba_extents where segment_name ='_SYSSMU12_1501422084$' group by bytes order by 1;
BYTES/1024 COUNT(*)
---------- ----------
64 2
1024 1
在数据库中有大事务的时候会报两种undo的错
1)、undo撑爆了,size级别
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
此时的处理相对简单,重建undo即可
1. 启动SQLPLUS,并用sys登陆到数据库。
#su - oracle
$>sqlplus / as sysdba
2. 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间:
SQL> show parameter undo_
3. 确认UNDO表空间;
SQL> select name from v$tablespace;
NAME
------------------------------
UNDOTBS1
4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置;
select file_name, bytes/1024/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';
5. 创建新的UNDO表空间,并设置自动扩展参数;
create undo tablespace undotbs2 datafile '/u01/oradata/undotbs2_01.dbf' size 30G reuse autoextend on next 100m maxsize 64G;
6. 切换UNDO表空间为新的UNDO表空间 , 动态更改spfile配置文件;
alter system set undo_tablespace=undotbs2 scope=both;
7.验证当前数据库的 UNDO表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
8. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE;
select t.segment_name, t.tablespace_name, t.segment_id, t.status
from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
_SYSSMU1$ UNDOTBS1 1 OFFLINE
_SYSSMU2$ UNDOTBS1 2 OFFLINE
......
_SYSSMU33$ UNDOTBS1 33 OFFLINE
_SYSSMU34$ UNDOTBS1 34 OFFLINE
_SYSSMU35$ UNDOTBS1 35 OFFLINE
上面对应的UNDOTBS1还原表空间所对应的回滚段均为OFFLINE
9.到$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.
10. 删除原有的UNDO表空间;
查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。
select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;
查询结果为空的话就能删除。
检查UNDO Segment状态;
select usn,
xacts,
rssize / 1024 / 1024 / 1024,
hwmsize / 1024 / 1024 / 1024,
shrinks
from v$rollstat
order by rssize;
若undo表空间中还存在回滚的对象,说明有session正在使用。
有可能遇到30013错误!
drop tablespace undotbs1 including contents and datafiles;
11. os级别释放undo数据文件;
到root下执行
lsof |grep /u01/oradata/undotbs01.dbf
lsof |grep /u01/oradata/undotbs01.dbf |awk '{printf"kill -9 "$2"\n"}'
2)、undo撑爆了,count级别
[oracle@ecardtestdb1 ~]$ oerr ora 01628
01628, 00000, "max # extents (%s) reached for rollback segment %s"
// *Cause: An attempt was made to extend a rollback segment that was
// already at the MAXEXTENTS value.
// *Action: If the value of the MAXEXTENTS storage parameter is less than
// the maximum allowed by the system, raise this value.
当有大事务时,rollback segment中的小extent 非常容易达到回滚段的上限,而导致无法拓展。
如果是第二种情况,则更加的简单
1、尝试另开会话,重新执行,主要看选择了哪一个回滚段,并且给回滚段在max_extents内可以完成该事物,则成功;否则还是会报相同的错误
2、批量提交(使用了游标的count属性),避免大sql的出现
DECLARE
CURSOR cur_fp_commit IS
SELECT * FROM t1;
TYPE recd IS TABLE OF t1%ROWTYPE;
recs recd;
BEGIN
OPEN cur_fp_commit;
WHILE (TRUE) LOOP
FETCH cur_fp_commit BULK COLLECT
INTO recs LIMIT 100;
FORALL i IN 1 .. recs.COUNT
INSERT INTO test VALUES recs (i);
COMMIT;
EXIT WHEN cur_fp_commit%NOTFOUND;
END LOOP;
CLOSE cur_fp_commit;
END;
/
3、shrink区数量比较多的回滚段
alter rollback segment "_SYSSMU24$" shrink;
4、重建undotablespace 与上述步骤一致