oracle创建索引时碰到这个问题,考虑2部分问题,第一为temp表空间不足,第二为索引表空间不足。
排查思路如下:
1、查看索引占用表空间大小
select (bytes/1024/1024/1024)||'G',a.*
from dba_segments a
where a.owner = 'XXX'
and a.segment_name like 'XXX%'
and a.segment_type = 'INDEX';
2、查看表空间使用情况
SELECT a.tablespace_name "表空间名称",
total / (1024 * 1024) "表空间大小(M)",
free / (1024 * 1024) "表空间剩余大小(M)",
(total - free) / (1024 * 1024 ) "表空间使用大小(M)",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
如果发现表空间不够了,扩充之;
1、查看表空间文件位置
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE, a.BYTES/ 1024 / 1024/1024 "文件大小(G)"
FROM dba_data_files a
where a.TABLESPACE_NAME = 'XXX';
2、扩表空间
alter tablespace XXXadd datafile '/oracle/oradata/XXX/XXX.dbf' size 30G;
查看临时表空间文件位置语句:
select tablespace_name,
file_name,
user_bytes/bytes,
bytes / 1024 / 1024/1024 "file_size(M)",
autoextensible
from dba_temp_files t
where t.TABLESPACE_NAME = 'TEMP';
扩充临时表空间语句
alter tablespace temp add tempfile '/oracle/oradata/XXX/temp16.dbf' size 30G;