ORACLE临时表空间使用率过高的原因及解决方案转

1、ORACLE临时表空间使用率过高的原因及解决方案在数据库的日常学习中，发现公司生产数据库的默认临时表空间temp使用情况到达了 30G使用 率到达T100%待调整为32GW，使用率还是为100%寻致磁盘空间使用紧张。根据临时表空间的主要是对临时 数据进行排序和缓存临时数据等特性，待重启数据库后，temp会自动释放。于是想通过重启数据库的方式来缓解这种情况，但是重启数据库之后，发现临时表空间temp的使用率还是100%一点没变。虽然运行中 应用暂时没有报什么错误，但是这在一定程度上存在一定的隐患，有待解决该问题。由于临时表空间主要使用在以下儿种情况：1、order by or group by

2、disc sort 占主要咅盼；2、索引的创立和重创立；3、distinct 操作；4、union & in tersect & minus sortj oins ；5、Analyze操作；6、有些异常也会引起TEM的暴涨。Oracle临时表空间暴涨的现象经过分析可能是以下儿个方面的原因造成的：1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。2?查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查

3、询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。3 ?对查询的某些字段没有建立索引。Oracle中,如果表没有索引，那么会将所有的数据都复制到临时表 空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。总结：1? SQ语句是会影响到磁盘的消耗的，不当的语句会造成磁盘暴涨。2?对查询语句需要仔细的规划，不要想当然的去定义一个查询语句，特别是在可以提供用户自定义查询的软件中。3?仔细规划表索引。如果临

4、时表空间是 temporary的，空间不会释放，只是在 sort结束后被标记为free的， 如果是permaner的，由SMC负责在sort结束后释放，都不用去手工释放的。查看有哪些用户和 SQ导致TEMP 增长的两个重要视图：v$ sort_usage和 v$sort_segment通过查询相尖的资料，发现解决方案有如下儿种：一、重建临时表空间tempTemporary tablespac是不能直接drop默认的临时表空间的，不过我们可以通过以下方法到达。查看LI前的 Temporary TablespaceSQL> select n ame from v$tempfile;NAMED

5、:0RACLE0RADATA0RCLTEDBTO1SQL> select user name, temporary.tablespace from dba_users:USERNAME TEMPORAR Y.TABLESPACEMGMT VIEW TEMPSYS TEMPSYSTEM TEMPDBSNMP TEMPSYSMAN TEMP1. 创立中转临时表空间create temporary tablespace TEMTPEI MPFILE 'D:0RACLE0RADATA0RCLtempS0IZ2E.D5B1F2' M REUSE AUTOOEXNTE NEXT IM

6、MAXSIZE UNLIMITED;2. 改变缺省临时表空间为刚刚创立的新临时表空间 tempi alter database default temporary tablespace tempi;3. 删除原来临时表空间drop tablespace temp including contents and datafiles;4. 重新创立临时表空间create temporary tablespace TEMP TEMPFILE 'D:0RACLE0RADATA0RCLtemp01.DBF' SIZE 512M REUSE NEXT IM MAXSIZE UNLIMITED

7、;5. 重置缺省临时表空间为新建的temp表空间alter database default temporary tablespace temp;6. 删除中转用临时表空间drop tablespace tempi including contents and datafiles;以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正的治本的方法是找出数据库中消耗资源比拟大的 sql 语句，然后对其进行优化处理。下面是查询在 sort 排序区使用的执 行耗 时的 SQL :Selectse. username, se. sid, su. extents, su ? bl

8、ocks*to_number (rtrim(p ?value)asSpace, tablespace, segtype,sql_textfrom v$sort_usage su, v$parameter p,v$session se,v$sql swhere p.nam_e, db_block_size'and su. session_addr=se ? saddrand s. hash_value=su. sqlhashand s.address=su. sqladdrorder by se. username, se. sid;或是：Select su. username, su.

9、 Extents, tablespace, segtype, sql_textfrom v$sort_usage su, v$sql sWhere su. SQL二s. SQL_ID;注：如果原临时表空间无帀户使用(selecttablespace_name, current_users, total_blocks, used_blocks, free_blocks, free_blocks/total_bl ocks from v$sort_segment;), 如果是文件系统可以看看文件的时间戳。我们可以删除该表空间：如果原临时表空间还有用户在使用，你是删除不了这个表空间的！在一次生产环境

10、的临时表空间切换中，原临时表空间始终有用户在上面，即使我尖闭了前台程序，也还是有用户，新的临时表空间已经没有用户在使用了。我估计用户进程已经死在原临时表空间了，后来只有重新启动数据库才能把原来旧的临时表空间给删除。二、修改参数 (这个方案紧适用于 81及 81以下的版本 )修改一下TEM表空间的storage参数，让Smo进程观注一下临时段，从而到达清理和TEM表空间的目 的。SQL>aIter tablespace temp increase 1:SQL>aIter tablespace temp increase 0;三、 Ki 11 session1、 使用如下语句 a 查看

11、一下认谁在用临时段SELECT se. username, se. SID, se. serial#, se. sql_address, se. machine, se. program, su. TABLESPACE,su. segtype, su. CONTENTSfrom v$session se, v$sort_usage suWHERE se. sad二 su. session_addr2、kill 正在使用临时段的进程SQL>AIter system ki11 session sid, serial if ;3、把TEM表空间回缩一下SQL>AIter tablespa

12、ce TEMP coalesce;注：这处方法只能针对字典管理表空间 (Dictionary ManageTdablespace)o 于本地管理表空间 (LMT:LocaMl anagedTablespace)不需要整理的。9i以后只能创立本地管理的表空间。CREATE TABLESPACE TEST DATATFEILSETD0:1.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARYCREATE TABLESPACE TEST D，ADTA:TFEILSET01.dbf SIZE 5M EXTENT MANAGEMENT LOCAL;四、 使用诊断事件

13、，也是相对有效的一种方法1、查询事件代码SQL>select ts#, name from sys? ts$ ;TS# NAME 0 SYSTEM1 IW0TBS12 SYSAUX3 TEMP4 USERS5 UND0TBS22、执行清理操作SQL>alter session set events ' immediate trace name DROP_SEGMENTS lieV8：4'temp表空间的 TS#为 3, So TS#+ 1= 。4oracle临时表空间过大的原因Oracle临时表空间主要是用来做查询和存放一些缓存的数据的，磁盘消耗的一个主要原因是需要

14、对查询的结果进行排序，如果没有猜错的话，在磁盘空间的(内存)的分配上，Oracle使用的是贪心算法，如果 上次磁盘空间消耗到达1GE那么临时表空间就是1GE如果还有增长，那么依此类推，临时表空间始终保持在一个最大的上限。Oracle临时表空间暴涨的现象经过分析可能是以下儿个方面的原因造成的。1 ?没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而岀错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。2 ?查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数

15、会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和 数据过大，那么就会消耗非常大的临时表空间重建新的临时表空间并进行切换：SQL> createcreatemporary ted tablespace ?temp tabltempfile ' /opOTIcle/ogdata/c onn er/temp 1. dbf size 1OM; TablespaceSQL> alterTablespaceSQL> alterDatabase altered ?SQL> select user name, temporary_

16、tablespace from dba_users;USERNAMETEMPORARY_TABLESPACESYSTEMPSYSTEMTEMPOUTLNTEMPEY GLETEMPCSMIGTEMPTESTTEMPRFPADMTNTEMP13 rows selected ?如果原临时表空间无用户使用(selecttablespace_name, current_users, total_blocks, used_blocks, free_blocks, free_blocks/total Abl ocks from v$sort_segment;)如果是文件系统可以看看文件的时间戳。我们可以删

17、除该表空间：(如果原临时表空间还有用户在用，你是删除不了这个表空间的！在一次生产环境的临时表空间切换中，原临时表空间始终有用户在上面，即使我尖闭了前台程序，也还是有用户，新的临时表空间已经没有用户在使用了。我佔讣用户进程已经死在原临时表空间了。后来只有重新启动 数据库才能把原来旧的临时表空间给删除。)SQL> drop tablespace temp2;Tablespace droppe?SQL>SQL> select n ame from v$tempfile;NAME /opt/oracle/oradata/Co nn er/temp? dbf/opt/oracle/or

18、adata/C onn er/temp2 ? dbfSQL> select fi1 en ame, tablespace_name, bytes/1024/1024 NIB, autoexte nsible2 from dba_temp_files3 /FILE NAMETABLESPACE NAMEMB ALTOEXTENSIBLE/opt/oracle/oradata/C onn er/temp2 ? dbfTEMP20 NO/opt/oracle/oradata/Conner/tempi ? dbf TEMP 10 NOdrop tablespace temp including contents and datafiles: - 将表空间的内容和数据文件一起删 除。 下面是 查询在 sort 排序区使用的执行耗时的 SQL:Select se. username, se. sid, su. extents, su. blocks*to_number(rtrim(p ? value)as Space, tablespace, segtype, sql_textfrom v$sort_usage su, v$parameter p, v$sessi on se, v$sql s where p. n