oracle总结-临时表空间

oracle总结-临时表空间

      相信大家都遇到这种情况，临时表空间一直增啊增，严重者执行语句和过程时候出现如下错误。

          ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

那这时候怎么办呢？官方文档都写了：

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

不过我想说这样还不够，不能彻底解决问题。要从根本上解决问题，必须找到表空间扩展的原因，临时

表空间扩展时因为排序等操作在pga中无法解决，因此需要占用磁盘里面的临时表空间，这样不仅速度

慢，而且表空间一直扩展，对临时表空间管理造成不必要的麻烦。因此，可以根据内存的情况适当调整

pga的大小。那么究竟怎么样的pga大小才是合适的呢？it depennds。就想别人不能指挥你穿什么样的鞋码，因为他就算他知道你的鞋码，他也不知道什么才是合适你的。有人要说了，那这样也不行，那样也

不行，究竟要怎么样？

其实sga和pga的大小设置，已经有高人总结出来了，下面我会列出来。只不过不能死板硬套，毕竟“脚”都

不一样，比如你的服务器经常会有各类任务，肯定要具体情况具体分析。

大师们总结的是这样：

一般oltp应用为内存*0.8*0.8(sga)、内存*0.8*0.2(pga);olap应用为内存*0.8*0.5(sga)、内存*0.8*0.5(pga)；或者oltp sga=系统内存*70%*80% pga=sga*(10%-20%),olap sga=系统内存*80%*60%，pga=sga*(45%-65%)。

设置完之后，有人要问了，我的pga已经设置好了，但是还是增长，那这样就要手动监控调整临时表空间的

大小了。

监控代码：

SELECT D.TABLESPACE_NAME,d.file_name,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,file_name,file_id,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME,file_name,file_id) D,
(SELECT TABLESPACE_NAME,file_id,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME,file_id) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and d.file_id=f.file_id(+);

增加表空间大小：

alter  tablespace temp add datafile '/u01/oraclehome/xx.dbf' size 32g;

切换临时表空间：

1.查看死锁进程：

select sess.sid, 
       sess.serial#, 
       lo.oracle_username, 
       lo.os_user_name, 
       ao.object_name, 
       lo.locked_mode 
   from v$locked_object lo, 
   dba_objects ao, 
   v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

2.查杀：

alter system kill session '493,44511'; 

3.创建临时表空间：

create    temporary tablespace  temp1
tempfile 'E:\oracle\product\10.2.0\oradata\plmdemo\temp1.dbf'
size 1g
;

4.切换默认临时表空间：

alter database default temporary tablespace  temp1;

5.删除原来临时表空间：

drop tablespace temp including content and datafiles;

但是实际过程中这个删除表空间操作会执行很久，甚至卡死，我们可以中断，然后执行alter tablespace temp drop tempfile 'xxx.dbf',然后执行drop tablespace temp including content and datafiles,接着关闭

数据库，删除文件即可。