ITPub博客

首页 > 数据库 > 数据库开发技术 > 临时表空间的问题

临时表空间的问题

原创 数据库开发技术 作者:shiyihai 时间:2007-10-11 17:05:08 0 删除 编辑

国庆节前一数据库抛临时表空间的错误,见下:
Fri Sep 28 13:26:53 2007
ORA-3217 signalled during: Alter tablespace TEMP coalesce
...
Fri Sep 28 13:50:56 2007
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Fri Sep 28 14:06:20 2007
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Fri Sep 28 14:21:34 2007
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Fri Sep 28 14:30:43 2007
Thread 1 advanced to log sequence 14063

[@more@]

如下是我当时用到的一些查询跟踪语句,这里汇总一下:
--当前有多少临时表空间被使用
select (sum (blocks))*8/1000 "MB" from v$sort_usage;

--查找哪个session用了多少临时段
select * from (
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 = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10;

--查询临时文件的使用情况:
Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
d.file_name "Datafile name",
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2) "Free KB",
round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",
0 "Fragmentation Index"
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;

--9iLMT的TEMP空间需要从dba_temp_files中查看剩余空间:
SELECT D.TABLESPACE_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,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,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) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

8i,DMT的TEMP空间则需要从DBA_FREE_SPACE中查看剩余空间:
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;


排序空间是能重复使用的,比如说:你为临时表空间分配4608M的空间,它的使用率会一直上升到100%,之后不会降下来的(除非重新启动数据库)。当有新的排序要sort(disk)时,它会重复利用已分配的空间。比如:
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP 4608 589824 4608 100 0

另外,v$temp_extent_pool用完的extent会cache在v$temp_extent_pool中被重用,所以不用担心使用了100%的临时表空间没有空间。
从9i R2开始v$sort_usage就改名为V$TEMPSEG_USAGE,其实v$sort_usage和V$TEMPSEG_USAGE取的数据源是相同的,v$sort_usage 查看的是哪个session正在以什么方式使用临时表空间,且使用了多少块;是当前正在使用的临时表空间信息

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38542/viewspace-975983/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    235
  • 访问量
    1670143