返回顶部

欢迎来到菜鸟大明儿哥的博客

我们一起交流学习,不断提升自我

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 段

 

查询默认临时表空间:

 

SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

 

查询临时表空间状态:

 

SQL> 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 ‘/oradata/orcl/temp01.dbf’ autoextend on next 5m 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; 
GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP1                         TEMPTS1 
GROUP2                         TEMPTS2 

 3)将表空间从一个临时表空间组移动到另外一个临时表空间组: 
alter tablespace tempts1 tablespace group GROUP2 ; 
select * from dba_tablespace_groups; 

GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP2                         TEMPTS1 
GROUP2                         TEMPTS2 

 4)把临时表空间组指定给用户 
alter user scott temporary tablespace GROUP2; 

 5)在数据库级设置临时表空间 
alter database <db_name> default temporary tablespace GROUP2; 

 6)删除临时表空间组 (删除组成临时表空间组的所有临时表空间) 
drop tablespace tempts1 including contents and datafiles; 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP2                         TEMPTS2 

drop tablespace tempts2 including contents and datafiles; 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 

对临时表空间进行shrink(11g新增的功能) 
--将temp表空间收缩为20M 
alter tablespace temp shrink space keep 20M; 
--自动将表空间的临时文件缩小到最小可能的大小 
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’; 
 

 

 

查询默认临时表空间:

 

  SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

查询临时表空间状态:

  SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

 

posted @ 2020-06-29 11:30  菜鸟大明儿哥  阅读(11062)  评论(0编辑  收藏  举报