临时表空间的疑问

chunchun2012

现有一个数据库，建库时临时表空间（数据库默认临时表空间）为100多M，用了一段时间以后，空间自动扩展为4G，显示利用率为99%，重启后仍然如此，请问通过哪些步骤确认这一临时表空间的使用情况是否正确，如果增长过快，通过什么 方面可以解决？

Maclean Liu(刘相兵

运行以下脚本 并贴出输出内容：

1. REM tablespace report
   
2. 
   
3. set linesize 200
   
4. 
   
5. select a.tablespace_name,
   
6.        round(a.bytes_alloc / 1024 / 1024) megs_alloc,
   
7.        round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
   
8.        round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
   
9.        round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
   
10.        100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
    
11.        round(maxbytes / 1048576) Max
    
12.   from (select f.tablespace_name,
    
13.                sum(f.bytes) bytes_alloc,
    
14.                sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
    
15.           from dba_data_files f
    
16.          group by tablespace_name) a,
    
17.        (select f.tablespace_name, sum(f.bytes) bytes_free
    
18.           from dba_free_space f
    
19.          group by tablespace_name) b
    
20. where a.tablespace_name = b.tablespace_name(+)
    
21. union all
    
22. select h.tablespace_name,
    
23.        round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
    
24.        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
25.              1048576) megs_free,
    
26.        round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
    
27.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
28.              sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
    
29.        100 -
    
30.        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
    
31.              sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
    
32.        round(sum(f.maxbytes) / 1048576) max
    
33.   from sys.v_$TEMP_SPACE_HEADER h,
    
34.        sys.v_$Temp_extent_pool  p,
    
35.        dba_temp_files           f
    
36. where p.file_id(+) = h.file_id
    
37.    and p.tablespace_name(+) = h.tablespace_name
    
38.    and f.file_id = h.file_id
    
39.    and f.tablespace_name = h.tablespace_name
    
40. group by h.tablespace_name
    
41. ORDER BY 1
    
42. /
    
43. 
    
44.     SELECT d.tablespace_name "Name",
    
45.                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
    
46.                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
    
47.                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
    
48.                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
    
49.                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
    
50.            FROM sys.dba_tablespaces d,
    
51.                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
    
52.                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
    
53.           WHERE d.tablespace_name = a.tablespace_name(+)
    
54.             AND d.tablespace_name = t.tablespace_name(+)
    
55.             AND d.extent_management like 'LOCAL'
    
56.             AND d.contents like 'TEMPORARY'
    
57. /

复制代码

chunchun2012

好的，多谢，等我有机会登录到那个数据库收集了日志，再请教！

chunchun2012

另外，请问第一段查询语句能不能用在其他的表空间上？能不能大致告诉我一下，两段查询语句的作用，

Maclean Liu(刘相兵

"第一段查询语句能不能用在其他的表空间上？"

可以

两段查询语句的作用，

生成 详细的 表空间 报告

chunchun2012

那如果这两个语句用在其他的表空间上，是不是需要修改某些字段？
要改  AND d.contents like 'TEMPORARY' 么？

Maclean Liu(刘相兵

不需要 ， 建议你实际测试一下 会了解地更清楚一些

chunchun2012

好的，非常感谢maclean!

chunchun2012

今天在我自己的测试数据库中运行了一下，第一段语句运行结果显示
TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYSAUX                                  250            3          247            1           99        32768
SYSTEM                                  480            7          473             1         99         32768   
TEMP                                          24             24            0            100            0      32768
TESTTABSPA                            200           8         192           4          96         200
UNDOTBS1                               645         636           9          99           1       32768
USERS                                    5           5           0          91           9       32768

6 rows selected.

说明这一段查看是针对数据库中所有表空间的，而第二段查询的结果如下


Name                           Size (M)        HWM (M)       HWM %
------------------------------ --------------- ------------- -------
Using (M)     Using %
------------- -------
TEMP                                    24.000        23.000   95.83
.000    0.00

说明这个只是针对临时表空间的，要想让第二段查询也针对所有表空间，该怎么改呢？
另外，有没有关于 MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX，HWM这些概念的详细介绍？

chunchun2012

maclean你好，今天在业务环境中运行了一下，
第一段SQL语句运行结果如下

TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
GKDB                                                      5300              70          5230           1          99         32768
GKNEWDB                                           5000            3638        1362          73          27        5000
SYSAUX                                                  500                40            460             8           92        32768  
SYSTEM                                                  600               75              525           12         88        32768
TEMP                                                       3790            3731          59          98           2           32768  
UNDOTBS1                                           780              765           15           98              2          32768     
USERS                                                       18                 12              5             70             30         32768

第二段语句运行结果
Name                           Size (M)        HWM (M)       HWM %
------------------------------ --------------- ------------- -------
Using (M)     Using %
------------- -------
TEMP                                 3,790.000      3789.000   99.97
       58.000    1.53
以上运行结果说明了什么问题？temp表空间和GKDB表空间使用是否正常？

[ 本帖最后由 chunchun2012 于 2012-4-19 13:04 编辑 ]

Maclean Liu(刘相兵

第二段语句本身 就是专门针对 TEMPORARY tablespace 的 。

MEGS_ALLOC   ==》表空间已分配的空间大小 MB数
MEGS_FREE     ==》 表空间上空闲的空间大小 MB数
MEGS_USED    ==》　表空间上已使用的空间大小 MB数
PCT_FREE       ==》　表空间的空闲空间比例
PCT_USED      ==》　表空间上已使用空间的比例　
MAX　　　　　==>   表空间目前情况下允许扩展到的 最大尺寸


HWM (M)   ==》　临时表空间Temporary tablespace被使用到过的高水位

chunchun2012

maclean你好，今天在业务环境中运行了一下，
第一段SQL语句运行结果如下

TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX

------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
GKDB                                                      5300              70          5230           1          99         32768
GKNEWDB                                           5000            3638        1362          73          27        5000
SYSAUX                                                  500                40            460             8           92        32768  
SYSTEM                                                  600               75              525           12         88        32768
TEMP                                                       3790            3731          59          98           2           32768  
UNDOTBS1                                           780              765           15           98              2          32768     
USERS                                                       18                 12              5             70             30         32768

第二段语句运行结果
Name                           Size (M)        HWM (M)       HWM %
------------------------------ --------------- ------------- -------
Using (M)     Using %
------------- -------
TEMP                                 3,790.000      3789.000   99.97
       58.000    1.53
以上运行结果说明了什么问题？temp表空间和GKDB表空间使用是否正常？

wzhihua

我们这里的临时表空间有更严重的问题，请mac老大给看看。
SQL> select t.TABLESPACE_NAME, t.RELATIVE_FNO, (t.BYTES_USED+t.BYTES_FREE)/(1024*1024) total,t.BYTES_FREE/(1024*1024) free from V$TEMP_SPACE_HEADER T ;

TABLESPACE_NAME                RELATIVE_FNO      TOTAL       FREE               
------------------------------ ------------ ---------- ----------               
TEMP                                      3 32767.9844          0               
TEMP                                      4 32767.9844          0               
TEMP                                      5 32767.9844          0               
TEMP                                      2 32767.9844          0               

SQL> SELECT t.TABLESPACE_NAME name,t.FILE_ID, t.BYTES_CACHED/(1024*1024) cached,t.BYTES_USED/(1024*1024) used from V$TEMP_EXTENT_POOL t ;

NAME                              FILE_ID     CACHED       USED                 
------------------------------ ---------- ---------- ----------                 
TEMP                                    2          0          0                 
TEMP                                    3      32767          3                 
TEMP                                    4      32767          0                 
TEMP                                    5      32767          2                 

SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------               
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production               
PL/SQL Release 9.2.0.8.0 - Production                                          
CORE        9.2.0.8.0        Production                                                      
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production               
NLSRTL Version 9.2.0.8.0 - Production   
针对file_id=2的文件，
V$TEMP_SPACE_HEADER 中BYTES_FREE=0 可是V$TEMP_EXTENT_POOL 中BYTES_CACHED=0 。
我在resize时提示“ora-03297”；
mac老大给解释下这两个值的区别呗！

wzhihua

因为涉及公司业务系统，所以没执行mac老大脚本中其它表空间的信息。若是确实需要的话，我以别的方式提供。

Maclean Liu(刘相兵

请给出以上2个脚本的输出

对于TEMPORARY TABLESPACE 其实可以不必要去resize， 完全可以重建一个新的 TEMP TABLESPACE，并 切换到该新的TEMP Tablespace上；  原temp tbs可以drop掉， 也可以在下一次startup时再drop。

anbob

V$TEMPSEG_USAGE  看下什么在用吧

wzhihua

按照mac的建议，我把temp重建了。