如何解决Oracle临时表空间过大

共计 3053 个字符，预计需要花费 8 分钟才能阅读完成。

SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "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,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name like 'TEMP%';

– 查看临时表空间的总大小和最大扩展大小 (能看到数据文件)

select file_name,
tablespace_name,
bytes / 1024 / 1024 MB,
autoextensible,
maxbytes / 1024 / 1024 MAX_MB
from dba_temp_files;

– 增加临时表空间的大小

alter tablespace temp1 add tempfile '/data/prod/proddata/temp013.dbf' size 4G;
alter tablespace temp2 add tempfile '/data/prod/proddata/temp024.dbf' size 4G;

select *
from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';

create temporary tablespace temp3 tempfile '/data/prod/proddata/temp31.dbf' size 4G tablespace group temp;
create temporary tablespace temp4 tempfile '/data/prod/proddata/temp41.dbf' size 4G tablespace group temp;

–2.1 从默认临时表空间组 temp 中移除 temp1 和 temp2；

ALTER TABLESPACE temp1 TABLESPACE GROUP '';
ALTER TABLESPACE temp2 TABLESPACE GROUP '';

–2.2 删除临时表空间 temp1 和 temp2

drop tablespace temp1 including contents and datafiles;
drop tablespace temp2 including contents and datafiles;

–2.3 如果删除表空间的时候，hang 住的话，可以使用下列语句，先把运行在 temp 临时表空间的 sql 语句 kill 掉，这样的 sql 语句多为排序的语句

Select se.username,
se.sid,
se.serial#,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid;

–2.4 kill 相关进程

alter system kill session '584,23181';
alter system kill session '196,64972';
alter system kill session '262,19832';
alter system kill session '324,40273';
alter system kill session '326,38967';
alter system kill session '1266,54596';

or
– 重启 DB
– 关闭应用 –> 关闭监听 –>shutdown immediate
–startup–> 启动监听 –> 执行以下操作后打开应用

–2.5 创建临时表空间，并加入临时表空间组 temp

create temporary tablespace temp1 tempfile '/data/prod/proddata/temp11.dbf' size 4G tablespace group temp;
create temporary tablespace temp2 tempfile '/data/prod/proddata/temp21.dbf' size 4G tablespace group temp;

–2.6 给临时表空间组 temp 的成员 temp1，temp2，temp3，temp4 各增加一个成员。

alter tablespace temp1 add tempfile '/data/prod/proddata/temp12.dbf' size 4G;
alter tablespace temp2 add tempfile '/data/prod/proddata/temp22.dbf' size 4G;
alter tablespace temp3 add tempfile '/data/prod/proddata/temp32.dbf' size 4G;
alter tablespace temp4 add tempfile '/data/prod/proddata/temp42.dbf' size 4G;

–2.7 查看临时表空间组 temp

select * from dba_tablespace_groups;

–3.1 为每个临时表空间添加 4G 空间

alter tablespace temp1 add tempfile '/data/prod/proddata/temp13.dbf' size 4G;
alter tablespace temp2 add tempfile '/data/prod/proddata/temp23.dbf' size 4G;
alter tablespace temp3 add tempfile '/data/prod/proddata/temp33.dbf' size 4G;
alter tablespace temp4 add tempfile '/data/prod/proddata/temp43.dbf' size 4G;

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配

腾讯云新客低至 82 元 / 年，老客户 99 元 / 年

代金券：在阿里云专用满减优惠券