临时表空间

SET LINESIZE 160

COL TABLESPACE_NAME FOR A30

SELECT TABLESPACE_NAME                 AS TABLESPACE_NAME

        ,SUM(BYTES)/1024/1024/1024          AS "TBS_SIZE(G)"

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME;

临时表空间文件

SET LINESIZE 160

COL TABLESPACE_NAME FOR A30

COL FILE_NAME FOR A50

SELECT TABLESPACE_NAME                 AS TABLESPACE_NAME

        ,FILE_NAME                     AS FILE_NAME

        ,BLOCKS                        AS BLOCKS

        ,STATUS                        AS STATUS

        ,AUTOEXTENSIBLE                AS AUTOEXTENSIBLE

        ,BYTES/1024/1024/1024          AS "FILE_SIZE(G)"

        ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024)     AS "MAX_SIZE(G)"

        ,INCREMENT_BY                  AS "INCREMENT_BY"

        ,USER_BYTES/1024/1024/1024     AS "USEFUL_SIZE"

FROM DBA_TEMP_FILES;

临时表空间情况

SYS@edw1> 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                             8      2350848       54784     2296064

EDWTMP                          10     35318400      124032    35194368

谁在使用临时表空间：

Col PROGRAM for a20

Col MACHINE for a20

col username for a15

SELECT se.username,

       sid,

       serial#,

       sql_address,

       machine,

       program,

       tablespace,

       segtype,

       contents

FROM v$session se,

v$sort_usage su

WHERE se.saddr=su.session_addr

order by 7,1;

临时表空间上正在执行哪些SQL

Col tablespace name for a20

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;