Oracle临时表空间

最新推荐文章于 2024-03-17 05:19:20 发布

olnathen

最新推荐文章于 2024-03-17 05:19:20 发布

阅读量1.1k

点赞数

分类专栏： oracle 文章标签： temporary 临时表空间

oracle 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

oracle的临时表空间

Oracle临时表空间为何暴涨？

昨天在做测试的时候发现一个非常奇怪的问题：在程序的查询模块中做查询的时候，开始速度很快，但是过了一段时间以后速度就变慢，最后干脆就报错，不工作了。在排错的过程中，发现Oracle临时表空间暴涨，达到了几十个GB，在Oracle中对Session进行跟踪，发现磁盘空间还在不停的消耗，几乎是每隔5s，临时表空间就会增长500MB左右，最后报错的原因应该是因为没有磁盘空间可以分配造成的。这是一件十分恐怖的事情。

        我们知道Oracle临时表空间主要是用来做查询和存放一些缓存的数据的，磁盘消耗的一个主要原因是需要对查询的结果进行排序，如果没有猜错的话，在磁盘空间的（内存）的分配上，Oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB，如果还有增长，那么依此类推，临时表空间始终保持在一个最大的上限。像上文提到的恐怖现象经过分析可能是以下几个方面的原因造成的。
        1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。
        2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。
       3.对查询的某些字段没有建立索引。Oracle中，如果表没有索引，那么会将所有的数据都复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。
       针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。

       总结：
       1.SQL语句是会影响到磁盘的消耗的，不当的语句会造成磁盘暴涨。
       2.对查询语句需要仔细的规划，不要想当然的去定义一个查询语句，特别是在可以提供用户自定义查询的软件中。
       3.仔细规划表索引。
       如果有那位高人遇到过类似的问题，请给予您的建议。

1、临时表空间的作用：

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。

当临时表空间不足时，表现为运算速度异常的慢，并且临时表空间迅速增长到最大空间（扩展的极限），并且一般不会自动清理了。

2、迅速增长的原因：

一般原因是sql算法不正确，可能导致出现了迪卡尔积。

另外一个原因是视图的创建问题（本人遇到的正是该问题！）。Oracle数据库中多张表联合查询时，极限的表单数量为4张，一般将以为3张！有时需要大于4张表联合查询时，怎么办呢？可以建立子视图：

如view0(联合table0,table1,table2),view1(联合table0,table3,table4),view2(联合table0,table5,table6)

然后将view0、view1、view2联合成view。

这样可以解决临时表空间迅速增长的问题！

以上所述，如有不正确的地方，欢迎大家指正！

临时表空间使用注意:
1.临时表空间是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)
每个用户都有一个临时表空间
2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理

3.分配用户单独临时表空间,一般是针对大型产品数据库,OLTP数据库,数据库仓库
对于小型产品不需要单独制定临时表空间,使用默认临时表空间

注:如果临时表空间异常增涨很快,则要考虑是否应用的sql语句产生了笛卡儿积,可以通过stactpack 进行分析查出问题所在,从而保证数据库的正常运行

Oracle临时表空间过大的解决办法

Temporary tablespace是oracle里临时表空间，临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。

排序是很耗资源的，Temp表空间满了，关键是优化你的语句，尽量使排序减少才是上策。

Temp表空间满时的处理方法：

　　一、修改参数(仅适用于8i及8i以下版本)

　　修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。

SQL>alter tablespace temp increase 1;
　　SQL>alter tablespace temp increase 0;

alter tablespace temp default storage(pctincrease 1);
alter tablespace temp default storage(pctincrease 0);

　　二：kill session
　　1、使用如下语句a查看一下认谁在用临时段

SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,

su.segtype, su.CONTENTS FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr

　　2、kill正在使用临时段的进程
　　SQL>Alter system kill session 'sid,serial#';

　　3、把TEMP表空间回缩一下
　　SQL>Alter tablespace TEMP coalesce;

注：
这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间（LMT:Local Managed Tablespace），不需要整理的。9i以后只能创建本地管理的表空间。
CREATE TABLESPACE TEST DATAFILE 'D:/TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY
CREATE TABLESPACE TEST DATAFILE 'D:/TEST01.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL;

　　三、重启数据库库

　　库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。

　　四、使用诊断事件的一种方法,也是最有效的一种方法
　　1、确定TEMP表空间的ts#
　　SQL>select ts#, name from sys.ts$ ;
       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 UNDOTBS1
         2 SYSAUX
         3 TEMP
         4 USERS
         5 UNDOTBS2

　　2、执行清理操作
　　SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;

　　说明：
　　temp表空间的TS# 为 3, So TS#+ 1= 4

重建TEMP 表空间：
Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法来做。

查看目前的Temporary Tablespace

SQL> select name from v$tempfile;

NAME
———————————————————————
D:/ORACLE/ORADATA/TEST/TEMP01.DBF

SQL> select username,temporary_tablespace from dba_users;
USERNAME                    TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                  TEMP
SYS                                  TEMP
SYSTEM                         TEMP
DBSNMP                        TEMP
SYSMAN                         TEMP

1.创建中转临时表空间

create temporary tablespace TEMP1 TEMPFILE 'E:/ORACLE/ORADATA/ORCL/temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

2.改变缺省临时表空间为刚刚创建的新临时表空间temp1

alter database default temporary tablespace temp1;

3.删除原来临时表空间

drop tablespace temp including contents and datafiles;

4.重新创建临时表空间

create temporary tablespace TEMP TEMPFILE 'E:/ORACLE/ORADATA/ORCL/temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

5.重置缺省临时表空间为新建的temp表空间

alter database default temporary tablespace temp;

6.删除中转用临时表空间

drop tablespace temp1 including contents and datafiles;

7.如果有必要，那么重新指定用户表空间为重建的临时表空间

alter user arbor temporary tablespace temp;

查看表空间语句，不过查不出Temp表空间：

　　SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),
　　'990.99') "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 4 DESC