首页 > 数据库DBA > oracle知识 > oracle调优 >
oracle 11g 解决临时表空间占满问题
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;
需要命令窗口 执行,效果如下
执行前 临时表空间 使用大小132G
执行后 临时表空间 使用大小 9G
–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; --整理碎片回收空间 并连同表的级联对象一起整理(比如索引)