回滾段表空間不足。大事務導致現有回滾段耗盡,無法分配新擴展。
ALTER TABLESPACE rbs_ts ADD DATAFILE '/path/rbs02.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
SELECT tablespace_name, SUM(bytes)/1024/1024 MB_FREE FROM dba_free_space GROUP BY tablespace_name;
SELECT class, count FROM v$waitstat WHERE class IN ('system undo header','system undo block','undo header','undo block');
SELECT SUM(value) FROM v$sysstat WHERE name IN ('db block gets','consistent gets');
-- 若 class count / sum(value) > 1%,需增加回滾段
UNDO_RETENTION,避免手動管理回滾段。臨時表空間不足,或因碎片導致無法分配連續擴展。常見於大排序、哈希連接、建立索引、全表掃描等操作。
SELECT file_id, block_id, blocks, bytes FROM dba_free_space WHERE tablespace_name = 'TEMP';
ALTER TABLESPACE temp DEFAULT STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0);
ALTER TABLESPACE temp ADD TEMPFILE '/path/temp02.dbf' SIZE 1G AUTOEXTEND ON;
ALTER USER app_user TEMPORARY TABLESPACE temp_large;
DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES 修復位圖異常。