Oracle Temp表空间切换

monkey6 / 2023-07-17 / 原文

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;

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;