ORA-01652:无法通过128(在表空间TEMP中)拓展temp字段
1.问题现象
业务反馈系统报 ORA-01652:无法通过128(在表空间TEMP中)拓展temp字段;
2.问题原因
这个很明显是临时表空TEMP空间满了,导致不能拓展字段;
3.解决方法
3.1 查看表空间使用情况
SELECT
*
FROM
(
SELECT
a.tablespace_name,
round( a.bytes / 1024 / 1024, 2 ) total_bytes,
round( b.bytes / 1024 / 1024, 2 ) free_bytes,
round( a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024, 2 ) use_bytes,
round(( 1 - b.bytes / a.bytes ) * 100, 2 ) || '%' USE
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 UNION ALL
SELECT
c.tablespace_name,
round( c.bytes / 1024 / 1024, 2 ) total_bytes,
round( ( c.bytes - d.bytes_used ) / 1024 / 1024, 2 ) free_bytes,
round( d.bytes_used / 1024 / 1024, 2 ) use_bytes,
round( d.bytes_used * 100 / c.bytes, 2 ) || '%' USE
FROM
( SELECT tablespace_name, sum( bytes ) bytes FROM dba_temp_files GROUP BY tablespace_name ) c,
( SELECT tablespace_name, sum( bytes_cached ) bytes_used FROM v$temp_extent_pool GROUP BY tablespace_name ) d
WHERE
c.tablespace_name = d.tablespace_name
)
ORDER BY
tablespace_name;
3.2 查看表空间大小、位置、空间使用情况、空间拓展性
select *
from (
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space, round(maxbytes/(1024*1024),0) maxbytes_space, autoextensible from dba_data_files
union all
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space, round(maxbytes/(1024*1024),0) maxbytes_space, autoextensible from dba_temp_files
) a
order by tablespace_name,file_id;
3.3 为表空间增加文件
alter tablespace temp add tempfile 'E:/ORADATA/NCENVIRO/temp05.dbf' size 2048M reuse autoextend on next 100M;