查看正在使用TEMP表空间的会话

set line 132
set pagesize 49999
set wrap off
col USERNAME for a8
col tablespace for a10
Select se.username,se.sid,se.serial#,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    SERIAL#    EXTENTS      SPACE TABLESPACE SEGTYPE   SQL_TEXT
-------- ---------- ---------- ---------- ---------- ---------- --------- ----------------------------------------------------------
ADMIN           286      10515          1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           423       3010          1    1048576 TEMP       LOB_DATA  begin :id := sys.dbms_transaction.local_transaction_id; en
SDE             333        307          1    1048576 TEMP       LOB_DATA  SELECT num_prop_value FROM  SDE.server_co

 

 

set line 132
set pagesize 49999
set wrap off
col USERNAME for a6
col tablespace for a10
Select se.username,se.sid,se.serial#,pr.spid ,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 ,V$process pr
   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    SERIAL# SPID            EXTENTS      SPACE TABLESPACE SEGTYPE   SQL_TEXT
-------- ---------- ---------- ------------ ---------- ---------- ---------- --------- ---------------------------------------------
ADMIN           286      10515 11725                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 12754                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 10100                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 13239                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 13984                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 6976                  1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 12412                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 10102                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 12691                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL
ADMIN           286      10515 14145                 1    1048576 TEMP       LOB_DATA  SELECT COUNT(*) FROM DUAL

 

 

v$sort_usage 查看的是哪个session正在以什么方式使用临时表空间，且使用了多少块;是当前正在使用的临时表空间信息

 

 

 

SQL> select (sum (blocks))*8/1024 "MB" from v$sort_usage  ;

 

        MB

----------

     1.024

 

SQL> select   USERNAME,USER, TABLESPACE,BLOCKS from  V$sort_usage  ;

USERNAME       USER                           TABLESPACE     BLOCKS
-------- ------------------------------ ---------- ----------
SDE                      SYS                                 TEMP                    128