管理TEMP数据

SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------32    0       0SQL> select * from test_1 order by 1,2,3,4;---查看TEMP使用的块比例SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_blocks from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS FREE_BLOCKS/TOTAL_BLOCKS------------------------------- ------------- ------------ ----------- ----------- ------------------------TEMP1    2    121728 34432     87296 .717139853CURRENT_USERSNUMBERNumber of active users of the segment  ---活动的用户数TOTAL_BLOCKSNUMBERTotal number of blocks in the segmentUSED_BLOCKSNUMBERBlocks allocated to active sorts---查看排序的SESSION ID:SQL> select sid,username,program from v$session where saddr in (select session_addr from V$TEMPSEG_USAGE);       SID USERNAME  PROGRAM---------- ------------------------------ ------------------------------------------------32 SCOTT  sqlplus@june (TNS V1-V3)34 SCOTT  plsqldev.exev$session saddr :Session addressV$TEMPSEG_USAGE session_addr:Address of shared SQL cursor----查看排序会话的SID SQL_ID 和排序段类型SQL> select username,SESSION_ADDR,sql_id,segtype from V$TEMPSEG_USAGE ;USERNAME       SESSION_ SQL_ID      SEGTYPE------------------------------ -------- ------------- ---------SCOTT       2E7D6A24 bydf32qgqdwdu DATASCOTT       2E7DBEFC 0trztnfnjqgk5 SORT