Oracle 临时表空间暴满的原因与解决方法 |
您所在的位置:网站首页 › 大排量和小排量哪个更耐用 › 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,contentsFROM v$session se,v$sort_usage suWHERE 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 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; |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |