解决 Oracle 临时表空间过大有两种方法, 方法一增加临时表空间的大小, 方法二重建临时表空间, 解决临时表空间过大的问题
方案一: 增加临时表空间的大小
--1. 临时表空间的使用情况
- 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;
-- 方案二: 重建临时表空间, 解决临时表空间过大的问题
--0. 查看目前默认的临时表空间
- select *
- from database_properties
- where property_name = 'DEFAULT_TEMP_TABLESPACE';
--1. 创建中转临时表空间
- 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. 删除原临时表空间组中的临时表空间
--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 临时表空间组仍然使用 99.98%,
--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;
来源: http://www.linuxidc.com/Linux/2018-03/151245.htm