首页 > 代码库 > undo过大的常见处理方式
undo过大的常见处理方式
一、普通情况下的操作：
undo表空间处理方式一般采用如下：
1、创建新的undo表空间
create undo
tablespace undotbs3 datafile‘+ORA_DATA_02‘ size 30G;
alter tablespace
undotbs3 add datafile‘+ORA_DATA_02‘ size
30G;-------可以添加数据文件来增大表空间，也可以通过resize扩大数据文件来扩大表空间
2、切换undo表空间
ALTER SYSTEM
SET UNDO_TABLESPACE=UNDOTBS3
scope=both;
3、删除旧的undo表空间------操作到这一步其实就可以了，但是为了保证表空间还是原来的名字，就需要再来一遍创建、切换、删除旧的undo表空间
drop
tablespace UNDOTBS1 including contents and datafiles;
4、创建undo表空间
5、切换undo表空间
6、删掉旧的undo表空间
针对ASM管理的rac，不同节点使用不同undo表空间的，需要在每个节点进行修改（一般rac不会所有节点共用一个undo表空间）
二、特殊情况之一
这两天处理项目undo表空间问题时，在删除旧的undo表空间时报错（ORA-30013）
SQL> drop tablespace UNDOTBS1 including
contents and datafiles;
drop tablespace UNDOTBS1 including contents and
datafiles
*
ERROR at line 1:
ORA-30013: 还原表空间 ‘UNDOTBS1‘
当前正在使用中
以上情况的处理方式
1、这时候就需要查询回滚段情况了：
select
segment_name,owner,tablespace_name,status from dba_rollback_segs where
tablespace_name=‘UNDOTBS4‘ and status = ‘ONLINE‘;
----查看回滚段中的事物sid，serial#
select SID,
MACHINE,PADDR,SADDR,substr(username,1,10) username,serial#,segment_name from
v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and
xidusn=segment_id;
----oracle级别杀掉会话
alter system kill
session ‘SID,SERIAL#‘;
2、终极绝招(绝对不是重启试试)：
如果还杀不掉的话，就需要先杀掉操作系统级别的进程，再在oracle级别杀掉会话。
-----------------------如下查看更加详细的事物进程号（oracle级别、系统级别）----------------------
set
line 200
set pagesize 999
col Sid for 99999
col MACHINE for a15
col
ThreadID for 99999
col segment_name for a30
col tablespace_name for
a10
col db_username for a10
col OSUser for a10
col program for
a40
select s.Sid AS "Sid",
s.Serial# "Serial#",
p.spid
"ThreadID",
s.MACHINE,
seg.segment_name,
seg.tablespace_name,
seg.status,
substr(s.username,1,10)
db_username,
s.OSUser "OSUser",
s.Program AS "Program"
from
v$transaction,dba_rollback_segs seg,v$session s,v$process p
where
saddr=ses_addr and xidusn=segment_id and
p.addr=s.paddr(+);
三、赠送两个我认为写的比较好的常用的查询表空间的语句
-----查看数据库各个表空间的使用情况---------------------------------
SET linesize
200
col tablespace_name format a15
SELECT a.tablespace_name,
round(((a.bytes - b.bytes) * 100) / a.maxbytes,
2) "%
USED",
round(((a.maxbytes - a.bytes + b.bytes) * 100) /
a.maxbytes,
2) "% FREE",
round(a.maxbytes / 1024 /
1024 / 1024,
2) "TOTAL(G)",
round((a.bytes - b.bytes)
/ 1024 / 1024 / 1024,
2) "USED(G)",
round((a.maxbytes
- a.bytes + b.bytes) / 1024 / 1024 / 1024,
2)
"FREE(G)",
round(a.maxbytes / 1024 / 1024,
2)
"TOTAL(M)",
round((a.bytes - b.bytes) / 1024 / 1024,
2) "USED(M)",
round((a.maxbytes - a.bytes + b.bytes) / 1024 /
1024,
2) "FREE(M)"
FROM (SELECT
ddf.tablespace_name,
SUM(decode(ddf.autoextensible,
‘NO‘,
ddf.bytes,
(sqrt((ddf.bytes + ddf.maxbytes) *
(ddf.bytes
+ ddf.maxbytes)) +
sqrt((ddf.bytes - ddf.maxbytes)
*
(ddf.bytes - ddf.maxbytes))) / 2))
maxbytes,
SUM(ddf.bytes) bytes
FROM
dba_data_files ddf
GROUP BY ddf.tablespace_name) a,
(SELECT dfs.tablespace_name,
SUM(dfs.bytes) bytes
FROM dba_free_space dfs
GROUP BY dfs.tablespace_name) b
WHERE
a.tablespace_name = b.tablespace_name(+)
ORDER BY round(((a.bytes - b.bytes)
* 100) / a.maxbytes,
2) DESC;
-----查看数据库各个表空间的使用情况---------------------------------
SELECT D.TABLESPACE_NAME,
SPACE || ‘M‘ "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || ‘M‘
"USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2)
|| ‘%‘
"USED_RATE(%)",
FREE_SPACE || ‘M‘
"FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND
(SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS)
BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME)
D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) /
(1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP
BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME =
F.TABLESPACE_NAME(+)
UNION
ALL
SELECT
D.TABLESPACE_NAME,
SPACE || ‘M‘ "SUM_SPACE(M)",
BLOCKS
SUM_BLOCKS,
USED_SPACE || ‘M‘ "USED_SPACE(M)",
ROUND (NVL
(USED_SPACE, 0) / SPACE * 100, 2) || ‘%‘ "USED_RATE(%)",
NVL
(FREE_SPACE, 0) || ‘M‘ "FREE_SPACE(M)"
FROM ( SELECT
TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2)
SPACE,
SUM (BLOCKS) BLOCKS
FROM
DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT
TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2)
USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2)
FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY
TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER
BY 1;
undo过大的常见处理方式