关于oracle10.2.0.5.0rac temp文件占用大咨询

lxhbww

请教下有没哪位在生产环境成功进行oracle rac的temp重建的，能否告诉我下怎么操作？
生产环境为二台win2003 x64+oracle10.2.0.5.0rac 用em进入查看temp表空间分配3G，目前占用1G多，
但到操作系统目录查看本temp文件则占用63G，要怎么处理，通过em修改成2G也是一样，文件大小没表，em中则已改成2G.
能否用单机重建temp步骤进行rac的temp重建？即先建另一temp，然后改成默认temp,将用户默认temp改到新建的，原来的temp删除？

Maclean Liu(刘相兵

ODM Finding :

1. How to Shrink the Datafile of Temporary Tablespace [ID 273276.1]
   
2. 
   
3. Applies to:
   
4. Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.2 - Release: 8.1.7 to 11.2
   
5. Information in this document applies to any platform.
   
6. ***Checked for relevance on 16-Dec-2011***
   
7. Goal
   
8. The database has a program which performs a huge sort operation (e.g. end of the year reporting process).  This may cause the TEMP tablespace to grow and occupy most of the space on the file system.
   
9. 
   
10. In this example, the report process may run once or twice a year and there is no need to maintain/keep a huge tempfile.
    
11. 
    
12. The TEMP tablespace was created with datafiles (dictionary managed tablespace temporary) as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid the Error:
    
13.   ORA-1652 Text: unable to extend temp segment by %s in tablespace %s.
    
14. 
    
15. Attempts have been made to "alter database datafile .. resize" which fail with:  
    
16. 
    
17. Error: ORA 3297 : file contains <> blocks of data beyond requested RESIZE value
    
18. 
    
19. You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.
    
20. 
    
21. 
    
22. Solution
    
23. 1)  Create a new temporary tablespace with desired smaller size:
    
24. 
    
25. SQL> create temporary tablespace TEMP1 tempfile 'c:\temp01.dbf' size 100M extent management
    
26. local uniform size 128K;
    
27. 
    
28. 
    
29. 2)  If the original tablespace is a default temporary tablespace, set the new tablespace as default temporary tablespace for all users in the database:
    
30. 
    
31. SQL> alter database default temporary tablespace TEMP1;
    
32. 
    
33. 
    
34. 3)  If necessary, explicitly re-assign specific users to the new tablespace:
    
35. 
    
36. SQL> alter user <username> temporary tablespace TEMP1;
    
37. 
    
38. 
    
39. 4) Drop the old tablespace:
    
40. 
    
41. SQL> drop tablespace temp including contents;
    
42. 
    
43. Note:
    
44. In Oracle 9i and higher, the drop command can also drop datafiles at OS level:
    
45. 
    
46. SQL> drop tablespace temp INCLUDING CONTENTS AND DATAFILES;
    
47. 
    
48. 
    
49. NOTE:
    
50. Temporary tablespaces should appear "full" after a while in a normally running database.
    
51. Extents are allocated once and then managed by the system. Rather than doing the rather expensive operation of "space management" (data dictionary updates), the system will allocate an extent in TEMP and then keep it and manage it itself.
    
52. This is normal and to be expected and is not an indication that there is a lack of temporary space.

复制代码

Maclean Liu(刘相兵

步骤 ：

1. 在共享存储上 创建 新的temporary tablespace
create temporary tablespace TEMP1 tempfile 'shared_storage/temp' size 100M extent management
local uniform size 128K;

2.  在数据库级别修改默认临时表空间
alter database default temporary tablespace TEMP1;

3.  若之前有 显示地设置过 某个用户的临时表空间， 则再显示地alter回来
alter user <username> temporary tablespace TEMP1;


4. 在确认没有任何 磁盘排序disk sort的情况下 drop掉原临时表空间
drop tablespace temp including contents;

lxhbww

太感谢了，谢谢刘大。