所以,在处理以上操作时,dba需要加倍关注temp的使用情况,v$sort_segment字典可以记载temp的比较详细的使用情况,而v$sort_usage将会告诉我们是谁在做什么.
sql>select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------- ------------ ----------- -----------
TEMP 1 63872 30464 33408
sql>
SQL>select username,session_addr,sqladdr,sqlhash from v$sort_usage
USERNAME SESSION_ADDR SQLADDR SQLHASH
------------------------------ ---------------- ---------------- ----------
CYBERCAFE C0000000D7EF99E8 C0000000E1BFE970 4053158416
然后通过多表联接,我们可以找出更详细的操作:
SQL>select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE
------------------------------ ---------- ---------- ---------- ------------------------------- ---------
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------
CYBERCAFE 42 238 249561088 TEMP SORT
select 1 from sys.streams$_prepare_ddl p where ((p.global_flag=1 and :1 is null) or (p.global_flag=0 and p.usrid=:2)) and rownum=1
本例应该是由一些异常引起的,其实大多数情况下sort都会在几乎内结束,如果在sort操作的若干秒内刚好就捕获了该SQL,应该走狗屎运的事情,即你知道某个SQL将会发生sort操作,当你想捕抓它们时,发现它们已经sort完了,排序完毕后sort segment会被smon清除。但很多时间,我们则会遇到临时段没有被释放，temp表空间几乎满的状况,这时该如何处理呢？
