首页 > oracle知识 > oracle调优 >

oracle 11g 解决临时表空间占满问题

作者: 初见博客 分类: oracle调优 发布时间: 2021-07-12 16:29

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的暴涨。

7、用户执行imp/exp 导入导出操作时,会使用大量的temporary段

8、用户在rebuild index时

9、执行create table …… as 语句时

10、移动用户下的数据到别的表空间时

大量的排序操作可能会导致临时表空间大量增长。为了提高性能,对排序区进行物理分配后,将在内存中管理它们以避免以后的物理回收。

结果,磁盘中包含一个巨大的临时文件,直到将其删除。

解决方法1:使用较小的文件创建新的临时表空间,并将这个新的表空间设置为用户的默认临时表空间,然后删除旧的表空间。

--查询默认临时表空间:
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--查询临时表空间状态:
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

--改变缺省临时表空间
alter database default temporary tablespace TEMP1;

--删除原来的临时表空间
drop tablespace TEMP including contents and datafiles;

但是,这有一个缺点,即过程要求删除旧的临时表空间时不能存在活动的排序操作。

方法2:从Oracle Database11g 版本1 开始,可使用

ALTER TABLESPACE   TEMPNAME  SHRINK  SPACE 命令收缩临时表空间

TABLESPACE SHRINK TEMP FILE 命令收缩临时文件。

对于这两个命令,可以指定可选的KEEP 子句,该子句定义了表空间/临时文件可收缩到的下限。

如果忽略KEEP 子句,则只要满足其它存储属性,数据库就会尽可能尝试收缩表空间/临时文件(所有当前使用的区的总空间)。

此操作需联机执行。但是,如果所分配的当前使用的一些区超出了收缩估计值,系统将等待这些区被释放以完成收缩操作。

--收缩表空间,降低使用率
ALTER TABLESPACE REGENT_TEMP SHRINK SPACE;

需要命令窗口 执行,效果如下

0e565b241e9f72d2821a5ca0e82ad2c7

执行前 临时表空间 使用大小132G

14612e40ae9397e9a7ea597554b61424

执行后 临时表空间 使用大小 9G

1f56fb0e371e41756049a35e4e556e1f

–shrink 操作是将原本松散的数据存放结构,通过将表中靠后的行向前面的空闲块迁移,在完成后将完全空闲的区释放,并前置 HWM 到表中最后一个使用块的位置,从而实现松散表重新结构紧凑 –High Water Mark,HWM) 是Oracle(Segment)级别的概念。

在仅有DML(比如delete,insert)操作时,高水位线只会增长,不会降低。

具体来说,由于程序使用的delete语句不回收使用过的空间,数据虽然删除了,但是高水位线却没有降低,仍然处于之前的水位。
–shrink降低表的高水位,如果表空间上有大量表、索引被 drop(或者 truncate),导致表空间前半部分出现大量空闲空间,可以通过 shrink将靠后的表移动到前面的空闲空间,从而收缩数据文件

降低表的高水位方法

alter table TABLE_NAME shrink space; --整理碎片并回收空间
alter table TABLE_NAME shrink space compact; --只整理碎片 不回收空间
alter table TABLE_NAME shrink space cascate; --整理碎片回收空间 并连同表的级联对象一起整理(比如索引)

 

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注