233
135
ora-01652 无法通过128 扩展temp段怎么办？
2021-07-19 17:22:29
0点赞
0收藏
0评论
Oracle 11g数据库在用impdp还原时候报错：
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
报错原因是temp空间不够，增加temp空间即可。
SQL> alter database tempfile '/data/oracle/oradata/WMSTEST/WMS_USER_TEMP.ora' resize 25000m;
Database altered.
查看temp表页游空间使用情况：
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPAChttp://www.pizei.comE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
文章图片来源：
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
TEMP 10000 1280000 658 6.58 9342
SEC_DEV_TEMP 0000 1280000 100 1 9900
WMS_USER_TEMP 25000 3200000 1 0 24999
WMS_DEV_TEMP 10000 1280000 1866 18.66 8134
