查看TEMP使用情况

SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#    VALUE
---------- ---------- ----------
      1996     0        0

SQL> create index acct_dtl_af_idx4 on T_PM_ACCT_DTL_AF (acct_flag,data_date,acct_no_pk,cur_code) ;
  2  ;

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
------------------------------- ------------- ------------ ----------- ----------- ------------------------
TEMP         1    5884544  35072    5849472    .99403998

 

SQL> select username,user,session_addr,sql_id  from v$sort_usage;

USERNAME         USER         SESSION_ADDR     SQL_ID
------------------------------ ------------------------------ ---------------- -------------
CZCB          SYS         00000000A51AC000 ft9r0byfw6wbc

查看正在运行的SESSION_ID和SQL语句:

v$sort_usage.SESSION_ADDR=v$session.SADDR 表示SESSION的地址

select a.username, b.sid, a.session_addr, a.sql_id
  from v$sort_usage a, v$session b
 where a.SESSION_ADDR = b.SADDR;

查看对应的sql
 select * from v$sqlarea where sql_id='ft9r0byfw6wbc';