日志常见报错:ORA-01652: unable to extend temp segment(临时表空间不足)
快速核查与处理:
表空间使用率:
SELECT a.tablespace_name, ROUND(a.bytes/1024/1024,2) “总大小(MB)”, ROUND((a.bytes-b.bytes)/1024/1024,2) “已用(MB)”, ROUND((a.bytes-b.bytes)/a.bytes*100,2) “使用率%” FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name;
临时表空间使用与扩容思路:
SELECT tablespace_name, SUM(bytes_used)/1024/1024 “已用MB”, SUM(bytes_free)/1024/1024 “空闲MB” FROM v$temp_space_header GROUP BY tablespace_name;
活跃会话与长时 SQL(配合日志时间窗排查根因):
SELECT s.sid, s.username, s.status, s.machine, sq.sql_text FROM v$session s JOIN v$sql sq ON s.sql_id=sq.sql_id WHERE s.status=‘ACTIVE’ AND s.username IS NOT NULL ORDER BY sq.elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
工作负载与 SQL 调优:
使用 AWR 报告识别高负载 SQL、等待事件与瓶颈;结合 SQL Tuning Advisor 获取可执行优化建议。