临时表空间

# 临时段
```sql
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;
```
# 会话使用的排序空间
 ```sql
SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          TBS.block_size,
          T.tablespace
 ORDER BY sid_serial;
```

# 使用的临时空间
 ```sql
SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       T.blocks * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       T.sqladdr address,
       Q.hash_value,
       Q.sql_text
  FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
 WHERE T.session_addr = S.saddr
   AND T.sqladdr = Q.address(+)
   AND T.tablespace = TBS.tablespace_name
 ORDER BY S.sid;
```

江南汇总：
```sql
select c.tablespace_name,
       c.bytes / 1024 / 1024 / 1024 total_bytes_GB,
       (c.bytes - d.bytes_used) / 1024 / 1024 / 1024 free_bytes_GB,
       d.bytes_used / 1024 / 1024 / 1024 use_bytes_GB,
       to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
  from (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         group by tablespace_name) c,
       (select tablespace_name, sum(bytes_cached) bytes_used
          from v$temp_extent_pool
         group by tablespace_name) d
 where c.tablespace_name = d.tablespace_name;

select *
  from database_properties
 where property_name = 'DEFAULT_TEMP_TABLESPACE';
 
select tablespace_name,
       file_name,
       bytes / 1024 / 1024 / 1024 file_size_G,
       autoextensible
  from dba_temp_files;

--#正在读取的临时段数据
select se.inst_id 节点ID,
       se.username 用户名,
       se.MODULE 连接程序,
       se.MACHINE 连接电脑,
       se.sid 会话ID,
       su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 / 1024 as 大小_GB,
       su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as 大小_MB,
       su.tablespace 表空间,
       su.segtype 类型,
       s.sql_text SQL语句
  from gv$sort_usage su, gv$parameter p, gv$session se, gv$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,大小_GB desc;

--#历史数据临时段数据
select *
  from (select t.SAMPLE_TIME 时间,
                s.PARSING_SCHEMA_NAME 用户,
                t.sql_id SQL_ID,
                t.sql_child_number as sql_child,
                round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,
                round(t.temp_space_allocated /
                      (select sum(decode(d.autoextensible,
                                         'YES',
                                         d.maxbytes,
                                         d.bytes))
                         from dba_temp_files d),
                      2) * 100 || ' %' as temp_pct,
                t.program,
                t.module,
                s.SQL_TEXT
           from v$active_session_history t, v$sql s
          where /*t.sample_time > to_date('2022-04-24 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
                    and t.sample_time < to_date('2022-04-24 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
                    and*/
          t.sample_time > sysdate - 24
       and t.temp_space_allocated is not null
       and t.sql_id = s.SQL_ID
          order by t.temp_space_allocated desc)
 where rownum < 100

临时表空间sysaux
##查询位置以及大小
sELECT t.bytes/1024/1024 mb,t.user_bytes/1024/1024 mbs,t.* FROM DBA_TEMP_FILES t;

##查询占用
SELECT se.username,
       se.SQL_ID,
       sid,
       serial#,
       sql_address,
       machine,
       program,
       tablespace,
       segtype,
       contents,
       'alter system kill session' || '''' || sid || ',' || serial# || '''' || ';'
  FROM v$session se, v$sort_usage su
 WHERE se.saddr = su.session_addr;

##收缩临时表空间
 Alter tablespace TEMP coalesce;--或者
对临时表空间进行shrink（11g新增的功能） 
--将temp表空间收缩为20M 
alter tablespace temp shrink space keep 20M; 
--自动将表空间的临时文件缩小到最小可能的大小 
ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

```