执行drop tablespace including contents and datafiles卡住
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of ASHSIZE to a sufficiently large value. Currently, ASH size is 67108864 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:select total_size,awr_flush_emergency_count from v$ash_info;
this my select
TOTAL_SIZE AWR_FLUSH_EMERGENCY_COUNT
---------- -------------------------
1
TOTAL_SIZE忘记了，没有及时明天，需明天补上。我执行的语句drop tablespace TEMP including contents and datafiles，当前默认临时表空间已设为别的，语句卡住一个钟头没有执行完。
这个需要确定临时表空间的使用 v$sort_usage ，我记得有个bug,但是没补丁 在单实例数据库中，试图drop temp tablespace的时候可能也会遇到类似问题
删除时候长时间等待enq: TS – contention，查看GV$lock找出阻塞会话为SMON进程，等待事件smon timer;
解决方法：
1、 查看temp表空间使用情况，杀掉session v$session,v$sort_usage
2、 设置drop segments事件，手工清理，ALTER SESSION SET events'immediate trace name drop_segments level tablespace_number+1';
最后我杀掉session好了，网上查好像是临时表空间切换的时候，如果有数据在临时表空间比如排序等操作没处理完的话，会出现这种情况。 厉害，学习了。
页:
[1]