Oracle Temp表空间切换

1.查看临时表空间情况

-- 查看数据库默认表空间
SELECT PROPERTY_NAME, PROPERTY_VALUE
  FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

-- 查看现有数据库临时表空间
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'TEMPORARY';

-- 查看临时表空间及临时表空间数据文件情况
SELECT TABLESPACE_NAME, FILE_NAME,BYTES / 1024 / 1024 AS SIZE_MB,STATUS FROM DBA_TEMP_FILES;

-- 查看用户临时表空间
SELECT username, temporary_tablespace FROM dba_users;

-- 查看临时表空间使用情况
/* Formatted on 2023/7/19 下午 04:57:37 (QP5 v5.163.1008.3004) */
SELECT T1.TABLESPACE_NAME,
       t1.TOTAL_SIZE_MB,
       NVL (t2.used_mb, 0),
       ROUND (NVL (t2.used_mb, 0) / t1.TOTAL_SIZE_MB, 2)*100||'%' AS used_percent
  FROM    (  SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size_mb
               FROM dba_temp_files
           GROUP BY TABLESPACE_NAME) t1
       LEFT JOIN
          (  SELECT tablespace, SUM (blocks) * 8 / 1024 AS used_mb
               FROM v$sort_usage
           GROUP BY tablespace) t2
       ON t1.tablespace_name = t2.tablespace;

2.新建临时表空间

CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE  '/data/monkey/temp02.dbf' SIZE 10M AUTOEXTEND OFF;

3.切换数据库默认表空间

alter database default temporary tablespace temp02;

4.查看在用旧临时表空间会话

-- 查看
SELECT * FROM v$session where saddr in (SELECT session_addr FROM v$sort_usage WHERE tablespace='TEMP');

-- 杀会话
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid ||','|| serial# || '''immediate;' FROM v$session where saddr in (SELECT session_addr FROM v$sort_usage WHERE tablespace='TEMP2');

5.删除旧临时表空间

注意：4步骤的会话没有杀掉的话，在RESIZE时报ORA-03297错误，在删除时，会报正在被使用错误

-- RESIZE
ALTER DATABASE TEMPFILE '/data/monkey/temp01.dbf' RESIZE 100M;
-- 删除
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

6.其他

6.1.修改某个用户临时表空间

alter user scott temporary tablespace temp;

6.2.查看占用临时表空间多的会话和sql

/* Formatted on 2023/7/17 下午 02:48:43 (QP5 v5.163.1008.3004) */
  SELECT /*+rule*/se.username,
         se.sid,
         su.blocks * TO_NUMBER (RTRIM (p.VALUE))/1024/1024 AS Space_MB,
         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 Space_MB desc;

  SELECT s.sid,
         s.serial#,
         s.username,
         SUM (t.blocks) * 8192 / 1024 / 1024 mb_used
    FROM v$sort_usage t, v$session s
   WHERE s.saddr = t.session_addr AND t.tablespace = 'TEMP'
GROUP BY s.sid, s.serial#, s.username
ORDER BY mb_used DESC;

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks,b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace)/1024/1024 USE_MB, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'T_TEMP_02'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value order by use_mb desc;