【Oracle SQL】临时表空间管理

---

一、查询临时表空间使用率

SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
       TT.TOTAL - TU.USED                                    AS "FREE(G)",
       TT.TOTAL                                              AS "TOTAL(G)",
       ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
       ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME, 
              allocated_space / 1024 / 1024 / 1024 USED
       FROM dba_temp_free_space) TU ,
     (SELECT TABLESPACE_NAME,
              SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
       FROM DBA_TEMP_FILES
       GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

二、查询临时表空间占用

1.查询真正消耗高temp表空间的sql语句

--查询真正消耗高temp表空间的sql语句
SELECT distinct a.*, sq.sql_id, sq.sql_text
  FROM (SELECT su.tablespace,
               s.sid,
               s.serial#,
               s.username,
               s.osuser,
               s.status,
               s.module,
               s.program,
               s.machine,
               sum(su.blocks) * tbs.block_size / 1024 / 1024 used_mb,
               su.segtype,
               s.logon_time,
               s.saddr
          FROM v$sort_usage    su,
               v$session       s,
               v$process       p,
               dba_tablespaces tbs
         WHERE su.session_addr = s.saddr
           AND s.paddr = p.addr
           AND su.tablespace = tbs.tablespace_name
         GROUP BY su.tablespace,
                  s.sid,
                  s.serial#,
                  s.username,
                  s.osuser,
                  s.status,
                  s.module,
                  s.program,
                  s.machine,
                  tbs.block_size,
                  su.segtype,
                  s.logon_time,
                  s.saddr) a,
       x$ktsso kt,
       v$sql sq
 where a.saddr = kt.ktssoses(+)
   and a.serial# = kt.ktssosno(+)
   and kt.ktssosqlid = sq.sql_id(+);

--或者直接利用下面的sql 
select k.inst_id "INST_ID",  
       ktssoses "SADDR",  
       sid,  
       ktssosno "SERIAL#",  
       username "USERNAME",  
       osuser "OSUSER",   
       ktssosqlid "SQL_ID",  
       ktssotsn "TABLESPACE",  
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",  
       --注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。  
       decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',   
          5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",  
       ktssofno "SEGFILE#",  
       ktssobno "SEGBLK#",  
       ktssoexts "EXTENTS",  
       ktssoblks "BLOCKS",  
       round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",  
       ktssorfno "SEGRFNO#"  
from x$ktsso k, v$session s,   
     (select value from v$parameter where name='db_block_size') p   
where ktssoses = s.saddr;

--通过以下语句查看一段时间前占用temp表空间高的sql
select *
  from (select to_char(t.sample_time, 'yyyy/mm/dd hh24:mi:ss.ff') sample_time,
               s.PARSING_SCHEMA_NAME,
               t.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('2023-11-13 07:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and t.sample_time < to_date('2023-11-13 08:00:00', 'yyyy-mm-dd hh24:mi:ss')
           and t.temp_space_allocated is not null
           and t.sql_id = s.SQL_ID
         order by t.temp_space_allocated desc)
 where rownum < 50
 order by temp_used desc;

2. 查询正在使用临时表空间的进程

--查询正在使用临时表空间的进程
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status, a.logon_time, program, tablespace, segtype, contents
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

--查看占用
SELECT   distinct se.username,
         se.sid,
         se.serial#,
         su.sql_id,
         se.status,
         su.extents,
         su.blocks * to_number(rtrim(p.value)) as Space,
         tablespace,
         su.segtype,
         sql_text,
         --s.sql_fulltext,
         se.logon_time,
         se.port    
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; 

2. 临时表空间对应的临时文件的使用情况

--临时表空间对应的临时文件的使用情况
SELECT ROUND((F.BYTES_FREE  + F.BYTES_USED)/1024/1024/1024, 2)                          AS "TOTAL(GB)",
       ROUND(((F.BYTES_FREE  + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)",
       D.FILE_NAME                                                                      AS "TEMP_FILE",
       ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2)                                    AS "USED(GB)"
FROM SYS.V_$TEMP_SPACE_HEADER F, DBA_TEMP_FILES D, SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
  AND F.FILE_ID(+) = D.FILE_ID
  AND P.FILE_ID(+) = D.FILE_ID;

三、Others：临时表空间管理

--1.临时表空间对应的数据文件
select * from dba_temp_files
  
--2.创建临时表空间
create TEMPORARY TABLESPACE TEMP1 TEMPFILE '/home/oracle/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100m MAXSIZE unlimited;

--3.临时表空间添加数据文件
alter tablespace TEMP1 add TEMPFILE '/home/oracle/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100m MAXSIZE unlimited;

-----------------------------------------------------------------------------------------------------------------------

--1.设置当前数据库的默认表空间为XXX
alter database default temporary tablespace TEMP1;

--2.删除表空间，包括数据文件
drop tablespace TEMP including contents and datafiles;

--3.临时表空间添加临时数据文件
alter database  tempfile '/home/oracle/temp01.dbf' AUTOEXTEND ON NEXT 10m MAXSIZE unlimited;