临时表空间的管理与受损恢复

　　3、临时表文件大小和已使用空间

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 /1024) "Used (G)"                       
        FROM   v$sort_usage                                                               
        GROUP  BY tablespace) t2                                                          
WHERE  t1."Tablespace" = t2.tablespace(+);                                                
                                                                                          
                                                                                          
Tablespace                     Total (G)          Used(G)   Free (G)                      
------------------------------ --------------- ---------- ----------                      
GOEX_TEMP                               31.999          1     30.999                      
FIX_TEMP                                 0.098          0       .098                      
TEMP                                     0.195          0       .195                      

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

-->使用临时段的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;                                                    
                                                                            
-->下面的查询也可以查询谁正在使用临时段                                     
 col username format a15                                                   
 col machine format a15                                                    
 col program format a30                                                    
 col tablespace format a15                                                 
 set linesize 160                                                          
 SELECT s.username                                                         
       ，s.sid                                                              
       ，s.serial#                                                          
       ，s.sql_address                                                      
       ，s.machine                                                          
       ，s.program                                                          
       ，su.tablespace                                                      
       ，su.segtype                                                         
       ，su.contents                                                        
 FROM   v$session s， v$sort_usage su                                       
 WHERE  s.saddr = su.session_addr;                                         
                                                                            
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;                                                          

　　5、修改默认的临时表空间

　　alter database default temporary tablespace tablespace_name;

　　6、对于过度使用临时表空间，在允许的情况下，可以杀掉其session

　　alter system kill session 'sid，serial#';