Oracle drop 临时表空间时长时间没反应

修改Oracle 默认临时表空间时，要drop一个临时表空间，长时间没反应，应该是有会话占用这个临时表空间，需要手动 alter session kill 掉。

用如下命令查询：

select srt.tablespace,
       srt.segfile#,
       srt.segblk#,
       srt.blocks,
       a.sid,
       a.serial#,
       a.sql_id,
       a.username,
       a.osuser,
       a.status
  from v$session a, v$sort_usage srt
 where a.saddr = srt.session_addr
       and srt.tablespace = 'TEMP1'
 order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;

--把对应会话kill后再执行如下：
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

其他临时表空间管理命令：

临时表空间使用率

SELECT temp_used.tablespace_name,
       round(total),
       used,       
       round(total - used) AS "Free",       
       round(nvl(total - used, 0) * 100 / total, 1) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used        
          FROM v$temp_space_header        
         GROUP BY tablespace_name) temp_used,       
       (SELECT tablespace_name,
               SUM(decode(autoextensible, 'YES', maxbytes, bytes)) / 1024 / 1024 total        
          FROM dba_temp_files        
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name;

其他相关临时表空间维护命令

create temporary tablespace temp_apex tempfile '/xxxxxx/temp_apex.dbf' size 1G reuse autoextend on next 1m maxsize 4G;

create temporary tablespace TEMP2 tempfile '/xxxxxxxxxxxx/temp2.dbf' size 4G reuse autoextend on next 1m maxsize 10G;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

ALTER TABLESPACE  TEMP2 TABLESPACE GROUP TEMP;

ALTER tablespace temp1 shrink tempfile '/xxxxxxxxxxxxxxx/temp19.dbf';

ALTER DATABASE tempfile '/xxxxxxxxxxx/temp19.dbf' resize 2g;

ALTER tablespace temp1 shrink tempfile '/xxxxxxxxxxxxxxxx/temp19.dbf';

ALTER tablespace temp1 shrink space;