oracle临时表空间作用

﹏ヽ暗。殇╰゛Y 2022-07-26 11:08 390阅读 0赞

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。

网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用：

索引create或rebuild

Order by 或 group by

Distinct 操作

Union 或 intersect 或 minus

Sort-merge joins

analyze

**查看临时表空间大小**

**查看临时表文件大小和已使用空间**

**select** t1."Tablespace" "Tablespace",  
t1."Total (G)" "Total (G)",  
nvl(t2."Used (G)", 0) "Used(G)",  
t1."Total (G)" - nvl(t2."Used (G)", 0) "Free (G)"  
**from**  
(  
**select** tablespace\_name "Tablespace", to\_char((**sum**(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"  
**from** dba\_temp\_files  
**group****by** tablespace\_name  
**union**  
**select** tablespace\_name "Tablespace", to\_char((**sum**(bytes/1024/1024/1024)),'99,999,990.900') "Total (G)"  
**from** dba\_data\_files  
**where** tablespace\_name **like**'TEMP%'  
**group****by** tablespace\_name  
) t1,  
(  
**select****tablespace**, round(**sum**(blocks)\*8/1024) "Used (G)" **from** v$sort\_usage  
**group****by****tablespace**  
) t2  
**where** t1."Tablespace"=t2.**tablespace**(+)

查看当前临死表使用空间大小与正在占用临时表空间的sql语句

**select** sess.SID, segtype, blocks\*8/1000 "MB" ,sql\_text  
**from** v$sort\_usage **sort**, v$session sess,v$sql **sql**  
**where****sort**.SESSION\_ADDR = sess.SADDR  
**and****sql**.ADDRESS = sess.SQL\_ADDRESS  
**order****by** blocks **desc**;

**select**'the ' || **name** || ' temp tablespaces ' || tablespace\_name ||  
       ' idle ' ||  
       round(100 - (s.tot\_used\_blocks / s.total\_blocks) \* 100, 3) ||  
       '% at ' || to\_char(**sysdate**, 'yyyymmddhh24miss')  
**from** (**select** d.tablespace\_name tablespace\_name,  
               nvl(**sum**(used\_blocks), 0) tot\_used\_blocks,  
               **sum**(blocks) total\_blocks  
          **from** v$sort\_segment v, dba\_temp\_files d  
         **where** d.tablespace\_name = v.tablespace\_name(+)  
         **group****by** d.tablespace\_name) s,  
       v$database;

**修改临时文件大小**

**select**'ALTER database TEMPFILE ' || file\_name || ' resize 100M ;'  
**from** dba\_temp\_files  
**where** tablespace\_name = 'ONLYDWTEMP';

ALTER database TEMPFILE '/oradata/ONLYDWTEMP06.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP07.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP08.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP09.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP10.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP01.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP02.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP03.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP04.dbf' resize 100M ;

ALTER database TEMPFILE '/oradata/ONLYDWTEMP05.dbf' resize 100M ;