加载中…Oracle临时表空间暴满
(2023-01-12 17:38:18)| 分类： Oracle |
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。数据库temp临时表空间增大，一般在数据安装时设置临时表空间大小，默认的情况下都是设置为自动增长。这样会引起一个问题：在数据库使用中temp表空间会自动扩展的越来越大，造成磁盘空间使用不足。
引起临时表空间增大主要使用在以下几种情况:
1、order by or group by (disc sort占主要部分);
2、索引的创建和重创建;
3、distinct操作;
4、union & intersect & minus sort-merge joins;
5、Analyze 操作;
6、有些异常也会引起TEMP的暴涨
--异常提示
ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段
ORA-01652: unable to extend temp segment by 128 in tablespace
TEMP2
--查询默认临时表空间:
SELECT * FROM database_properties where
property_name='DEFAULT_TEMP_TABLESPACE';
SELECT * FROM dba_temp_files where
tablespace_name='TEMP';
--查看临时表空间使用率
SELECT c.tablespace_name,
to_char(c.bytes/1024/1024/1024,'99,999.999')||' G'
total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999')
free_gb,
to_char(d.bytes_used/1024/1024/1024,'99,999.999')
use_gb,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
FROM
WHERE c.tablespace_name = d.tablespace_name;
--查询临时表空间状态:
select tablespace_name,file_name,bytes/1024/1024
file_size,autoextensible from dba_temp_files;
--方法1.重启实例
实例重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的
--方法2.增大临时文件大小
SQL> alter database tempfile
‘/oradata/orcl/temp01.dbf’resize 20000m;
注:数据文件最大为32GB,如果现有数据文件已达到32GB将无法resize成更大值,只能缩小
--方法3.将临时数据文件设为自动扩展
SQL> alter database tempfile '/u01/datafile/temp02.dbf'
autoextend on next 100m maxsize unlimited;
--方法4.查看一下谁在用临时段
SELECT
se.username,sid,serial#,sql_address,machine,program,tablespace,segtype,contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr;
SQL> Alter system kill session
'sid,serial#'; -- 杀掉进程
--把TEMP表空间回缩一下
SQL> alter tablespace TEMP coalesce;
--方法5.向临时表空间中添加数据文件
SQL> alter tablespace temp add tempfile
'/oradata/orcl/temp02.dbf' size 10000m;
--方法6.临时表空间组
1.创建临时表空间组:
create temporary tablespace tempts1 tempfile
'/home/oracle/temp1_02.dbf' size 2M tablespace group
group1;
create temporary tablespace tempts2 tempfile
'/home/oracle/temp2_02.dbf' size 2M tablespace group
group2;
2.查询临时表空间组：dba_tablespace_groups视图
select * from dba_tablespace_groups;
3.将表空间从一个临时表空间组移动到另外一个临时表空间组：
alter tablespace tempts1 tablespace group
GROUP2;
select * from dba_tablespace_groups;
4.把临时表空间组指定给用户
alter user scott temporary tablespace
GROUP2;
5.在数据库级设置临时表空间
alter database default temporary tablespace
GROUP2;
6.删除临时表空间组(删除组成临时表空间组的所有临时表空间)
drop tablespace tempts1 including contents and
datafiles;
select * from dba_tablespace_groups;
drop tablespace tempts2 including contents and
datafiles;
select * from dba_tablespace_groups;
对临时表空间进行shrink
--将temp表空间收缩为20M
alter tablespace temp shrink space keep 20M;
alter tablespace temp shrink space keep
1024M; --收缩临时表空间,11g新功能,注意使用keep参数
--自动将表空间的临时文件缩小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE
’/u02/oracle/data/lmtemp02.dbf’;
alter tablespace temp02 shrink tempfile
'/u01/datafile/temp02.dbf';
--尽可能的收缩临时表空间数据文件
alter tablespace temp02 shrink tempfile
'/u01/datafile/temp02.dbf' keep 1000m;
--使用keep参数
参考地址
https://www.cnblogs.com/zhm1985/p/13207071.html
喜欢
0
赠金笔
前一篇：Java中常见的数组排序方法