Oracle 臨時表空間查詢與維護指令大全（最完整版）

一、臨時表空間主要用途

• 索引建立/重建 (CREATE/REBUILD INDEX)
• 排序操作：ORDER BY、GROUP BY、DISTINCT
• 集合運算：UNION、INTERSECT、MINUS
• 排序合併連線 (Sort-Merge Joins)
• ANALYZE 指令

二、基本管理指令

1. 查看使用者預設暫存表空間

SELECT username, temporary_tablespace FROM dba_users;

2. 查看資料庫預設暫存表空間

SELECT * FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

3. 建立臨時表空間

-- 單一暫存檔
CREATE TEMPORARY TABLESPACE temp_data TEMPFILE '/oracle/oradata/db/temp_data.dbf' SIZE 50M;

-- 多暫存檔
CREATE TEMPORARY TABLESPACE temp TEMPFILE
  '/u02/oradata/orcl/temp01.dbf' SIZE 6144M,
  '/u02/oradata/orcl/temp02.dbf' SIZE 6144M;

4. 調整暫存檔大小

ALTER DATABASE TEMPFILE '/u2/oradata/spring/temp_data.dbf' RESIZE 1024M;

5. 變更資料庫預設暫存表空間

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/u02/oradata/orcl/temp101.dbf' SIZE 4056M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

6. 刪除臨時表空間

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

7. 縮減臨時表空間 (11g+)

-- 保留 20M
ALTER TABLESPACE temp SHRINK SPACE KEEP 20M;
-- 自動縮至最小
ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

三、臨時表空間過大時的重建流程

1. 建立中轉暫存表空間 TEMP1
2. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1; 並驗證使用者已切換
3. 刪除舊 TEMP 表空間
4. 重建新 TEMP 表空間
5. 切回 TEMP 並驗證

四、監控查詢

1. 查看誰在使用暫存表空間

SELECT se.username, se.sid, se.serial#, se.sql_address, se.machine, se.program,
       su.tablespace, su.segtype, su.contents
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;

2. 查看暫存表空間剩餘空間

SELECT tablespace_name, file_id,
       bytes_used/1024/1024 AS used_mb,
       bytes_free/1024/1024 AS free_mb
FROM v$temp_space_header;

3. 特定 SID 的暫存使用明細

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks,
       b.blocks * 32 / 1024 / 1024 AS size_mb,
       a.sid, a.serial#, a.username, a.osuser, a.status,
       c.sql_text, b.contents
FROM v$session a, v$sort_usage b, v$sql c
WHERE a.saddr = b.session_addr
  AND a.sql_address = c.address
ORDER BY b.blocks DESC;

4. 暫存表空間整體使用率

SELECT tmp_tbs.tablespace_name,
       SUM(tmp_tbs.total_mb) AS total_mb,
       SUM(used_tot.used_mb) AS used_mb,
       ROUND(SUM(used_tot.used_mb) / SUM(tmp_tbs.total_mb) * 100, 2) AS used_percent
FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
      FROM dba_temp_files GROUP BY tablespace_name) tmp_tbs,
     (SELECT tmp_used.tablespace,
             SUM(tmp_used.blocks * para.db_block_size)/1024/1024 AS used_mb
      FROM v$sort_usage tmp_used,
           (SELECT value AS db_block_size FROM v$parameter WHERE name = 'db_block_size') para
      GROUP BY tmp_used.tablespace) used_tot
WHERE tmp_tbs.tablespace_name = used_tot.tablespace
GROUP BY tmp_tbs.tablespace_name;

5. 依段類型 (SORT/LOB) 統計使用量

SELECT tmp_tbs.tablespace_name,
       used_tot.segtype AS temp_seg_type,
       SUM(tmp_tbs.total_mb) AS total_mb,
       SUM(used_tot.used_mb) AS used_mb,
       ROUND(SUM(used_tot.used_mb) / SUM(tmp_tbs.total_mb) * 100, 2) AS used_percent
FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
      FROM dba_temp_files GROUP BY tablespace_name) tmp_tbs,
     (SELECT tmp_used.tablespace, tmp_used.segtype,
             SUM(tmp_used.blocks * para.db_block_size)/1024/1024 AS used_mb
      FROM v$sort_usage tmp_used,
           (SELECT value AS db_block_size FROM v$parameter WHERE name = 'db_block_size') para
      GROUP BY tmp_used.tablespace, tmp_used.segtype) used_tot
WHERE tmp_tbs.tablespace_name = used_tot.tablespace
GROUP BY tmp_tbs.tablespace_name, used_tot.segtype;

五、查看資料庫預設永久/暫存表空間

SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE '%DEFAULT%';