SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SIZE 50M
3 AUTOEXTEND ON NEXT 10M MAXSIZE 500M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 1M;Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;Database altered.

— 1. 查看临时表空间信息
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces
WHERE contents = ‘TEMPORARY’;– 2. 查看临时表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_temp_files;– 3. 修改临时表空间大小
ALTER TABLESPACE temp
ADD TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp02.dbf’ SIZE 50M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;– 4. 调整临时表空间自动扩展
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;– 5. 重命名临时表空间
ALTER TABLESPACE temp_old RENAME TO temp_new;– 6. 删除临时表空间
DROP TABLESPACE temp_old INCLUDING CONTENTS AND DATAFILES;– 7. 更改用户的临时表空间
ALTER USER scott TEMPORARY TABLESPACE temp;– 8. 查看临时表空间组
SELECT * FROM dba_tablespace_groups;– 9. 将临时表空间添加到表空间组
ALTER TABLESPACE temp1 TABLESPACE GROUP temp_group;– 10. 从表空间组中移除临时表空间
ALTER TABLESPACE temp1 TABLESPACE GROUP ”;

— 1. 监控临时表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_temp_files
) GROUP BY tablespace_name;– 2. 监控临时表空间中的临时段
SELECT tablespace_name, segment_type, COUNT(*) AS segment_count
FROM dba_segments
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE contents = ‘TEMPORARY’
) GROUP BY tablespace_name, segment_type;– 3. 监控会话的临时表空间使用情况
SELECT s.sid, s.serial#, s.username, s.tablespace,
t.blocks * 8/1024 AS used_mb
FROM v$session s, v$sort_usage t
WHERE s.saddr = t.session_addr;– 4. 查看临时表空间的使用历史
SELECT * FROM dba_hist_tempstat_summary
ORDER BY snap_id DESC;– 5. 收缩临时表空间
ALTER TABLESPACE temp SHRINK SPACE;– 6. 收缩临时表空间到指定大小
ALTER TABLESPACE temp SHRINK SPACE KEEP 50M;– 7. 收缩临时数据文件
ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SHRINK SPACE;– 8. 检查临时表空间状态
SELECT file_name, status
FROM dba_temp_files;

— 案例：为大型数据仓库系统配置临时表空间

— 1. 创建主临时表空间
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 8M;– 2. 创建大型临时表空间
CREATE TEMPORARY TABLESPACE temp_large
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp_large01.dbf’ SIZE 2G
AUTOEXTEND ON NEXT 200M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 16M;– 3. 创建临时表空间组
CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp1_01.dbf’ SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 5G
TABLESPACE GROUP temp_group;CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp2_01.dbf’ SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 5G
TABLESPACE GROUP temp_group;– 4. 设置默认临时表空间组
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;– 5. 为特定用户分配大型临时表空间
ALTER USER data_warehouse_user TEMPORARY TABLESPACE temp_large;– 6. 监控临时表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_temp_files
) GROUP BY tablespace_name;– 7. 收缩临时表空间
ALTER TABLESPACE temp SHRINK SPACE;ALTER TABLESPACE temp_large SHRINK SPACE;

— 1. 临时表空间不足问题
— 查看临时表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_temp_files
) GROUP BY tablespace_name
ORDER BY used_percent DESC;– 解决方案：添加临时数据文件
ALTER TABLESPACE temp
ADD TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp03.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;– 2. 临时表空间碎片问题
— 检查临时表空间碎片
SELECT tablespace_name,
COUNT(*) AS extent_count,
MAX(blocks) AS max_blocks,
MIN(blocks) AS min_blocks,
AVG(blocks) AS avg_blocks
FROM dba_extents
WHERE tablespace_name IN (
SELECT tablespace_name
FROM dba_tablespaces
WHERE contents = ‘TEMPORARY’
) GROUP BY tablespace_name;– 解决方案：收缩临时表空间
ALTER TABLESPACE temp SHRINK SPACE;– 3. 临时表空间使用过高问题
— 查看会话的临时表空间使用情况
SELECT s.sid, s.serial#, s.username, s.tablespace,
t.blocks * 8/1024 AS used_mb
FROM v$session s, v$sort_usage t
WHERE s.saddr = t.session_addr
ORDER BY used_mb DESC;– 解决方案：终止占用大量临时空间的会话
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;– 4. 临时表空间组配置问题
— 查看临时表空间组
SELECT * FROM dba_tablespace_groups;– 解决方案：调整临时表空间组配置
ALTER TABLESPACE temp3 TABLESPACE GROUP temp_group;

— 1. 临时表空间无法扩展问题
— 检查临时表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_temp_files;– 解决方案：检查文件系统空间，添加临时数据文件

— 2. 临时表空间使用过高问题
— 查看当前使用临时表空间的SQL语句
SELECT s.sid, s.serial#, s.username, s.sql_id,
t.blocks * 8/1024 AS used_mb
FROM v$session s, v$sort_usage t
WHERE s.saddr = t.session_addr
ORDER BY used_mb DESC;– 查看SQL语句内容
SELECT sql_text FROM v$sql WHERE sql_id = ‘sql_id_value’;– 解决方案：优化SQL语句，减少排序操作

— 3. 临时表空间损坏问题
— 检查临时表空间状态
SELECT file_name, status
FROM dba_temp_files;– 解决方案：重建临时表空间
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp01.dbf’ SIZE 50M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;– 4. 临时表空间组故障问题
— 查看临时表空间组
SELECT * FROM dba_tablespace_groups;– 解决方案：重建临时表空间组
ALTER TABLESPACE temp1 TABLESPACE GROUP ”;ALTER TABLESPACE temp2 TABLESPACE GROUP ”;DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;CREATE TEMPORARY TABLESPACE temp1
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp1_01.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M
TABLESPACE GROUP temp_group;CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘/u01/app/oracle/oradata/ORCL/temp2_01.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M
TABLESPACE GROUP temp_group;