ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

记一次“ORA-1652”

1.1故障环境介绍

项目	Info
DB类型	RAC
版本	12.1.0.2
存储	ASM

1.2报错详情

Sat Jan 12 04:57:03 2019
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 

1.3故障处理
1.3.1
常规检查ORA报错三板斧
第一斧：检查Alert Log
==>Alert Log中显示的报错与上述内容一致，并无过多内容可以追踪
通过Log信息可以确认

• tablespace name=‘TEMP’
• 报错信息显示异常原因为该表空间无法扩展

针对上述信息可以确认下一步动作

1. 确认Tablespace=‘TEMP’,该空间大小
2. 确认该表空间是否开启自动扩展
3. 如果确认已开启自动扩展且已知表空间大小，则判断当时数据库的动作有无超过TEMP最大空间，即可定位至异常的原

1.3.2
检查’TEMP’表空间的大小

select * from dba_temp_files;

"FILE_NAME"	"FILE_ID"	"TABLESPACE_NAME"	"BYTES"	"BLOCKS"	"STATUS"	"RELATIVE_FNO"	"AUTOEXTENSIBLE"	"MAXBYTES"	"MAXBLOCKS"	"INCREMENT_BY"	"USER_BYTES"	"USER_BLOCKS"
"+DATA/FDCOFFDB/temp01.dbf"	1	"TEMP"	34358689792	4194176	"ONLINE"	1	"YES"	34359721984	4194302	80	34357641216	4194048
"+DATA/FDCOFFDB/temp02.dbf"	2	"TEMP"	34358689792	4194176	"ONLINE"	2	"YES"	34359721984	4194302	1	34357641216	4194048

可以看到该TEMP表空间下有两个DBF文件，每个大小为32GB，且均已开启自动扩展
综上可知，目前tablespace=‘TEMP’总大小为32*2=64GB

1.3.3错误定位
根据配置可知，数据库单次获取的数据量应该小于64GB，否则就会出现TEMP表空间不足的情况

查询在异常时间段内，占用资源超过60GB的SQL
(TEMP_SPACE_ALLOCATED >=64424509440)

select *
from
(
select INSTANCE_NUMBER iid,
to_char(SAMPLE_TIME,'mmdd hh24:mi:ss') SAMPLE_TIME,
to_char(SQL_EXEC_START,'mmdd hh24:mi:ss') SQL_EXEC_START,
SESSION_ID sess,
SESSION_SERIAL# serial#,
machine,
--program,
--XID,
--event,
sql_id,
round(TEMP_SPACE_ALLOCATED/1024/1024/1024) GB,
round(TEMP_SPACE_ALLOCATED/1024/1024)-lag(round(TEMP_SPACE_ALLOCATED/1024/1024),1,0) over (partition by INSTANCE_NUMBER,SESSION_ID,SESSION_SERIAL#,machine,program,SQL_EXEC_START order by SAMPLE_TIME)  diff
from dba_hist_active_sess_history 
 where 
 SAMPLE_TIME between TO_TIMESTAMP ('2019-01-12 03:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_TIMESTAMP ('2019-01-12 05:00:00', 'YYYY-MM-DD HH24:MI:SS')
--and sql_id ='dx4nqvbtu06bx'
and TEMP_SPACE_ALLOCATED >=64424509440
order by INSTANCE_NUMBER,SAMPLE_TIME,SQL_EXEC_START
)
where  diff >=1 order by GB desc;

查询结果如下

"IID"	"SAMPLE_TIME"	"SQL_EXEC_START"	"SESS"	"SERIAL#"	"MACHINE"	"SQL_ID"	"GB"	"DIFF"
1	"0112 04:55:44"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	119
1	"0112 04:56:34"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	46
1	"0112 04:56:44"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	44
1	"0112 04:56:14"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	43
1	"0112 04:56:04"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	47
1	"0112 04:55:54"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	122
1	"0112 04:56:24"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	49
1	"0112 04:56:54"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	64	52
1	"0112 04:55:34"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	120
1	"0112 04:54:13"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	72
1	"0112 04:54:33"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	81
1	"0112 04:54:23"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	78
1	"0112 04:55:24"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	103
1	"0112 04:54:03"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	67
1	"0112 04:55:04"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	96
1	"0112 04:54:54"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	109
1	"0112 04:54:44"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	72
1	"0112 04:53:53"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	55
1	"0112 04:55:14"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	63	116
1	"0112 04:53:43"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	30
1	"0112 04:53:33"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	31
1	"0112 04:53:23"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	17
1	"0112 04:53:13"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	16
1	"0112 04:53:03"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	15
1	"0112 04:52:43"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	16
1	"0112 04:52:33"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	136
1	"0112 04:52:23"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	185
1	"0112 04:52:13"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	135
1	"0112 04:52:03"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	124
1	"0112 04:51:53"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	116
1	"0112 04:51:43"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	111
1	"0112 04:51:33"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	109
1	"0112 04:52:53"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	62	14
1	"0112 04:51:13"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	61	118
1	"0112 04:51:23"	"0112 02:13:27"	1060	26639	"XXX"	"6sj3vbnfbay23"	61	120

从查询结果可以看出，导致异常的SQL_ID均为：6sj3vbnfbay23

继续根据SQL_ID查询出对应的SQL

select * from dba_hist_sqltext where sql_id='6sj3vbnfbay23';

查询结果如下

"DBID"	"SQL_ID"	"SQL_TEXT"	"COMMAND_TYPE"	"CON_DBID"	"CON_ID"
1343432988	"6sj3vbnfbay23"	"select * from xxx"	3	1343432988	0

1.3.4异常处理

• 处理步骤1
  优先确认相关的SQL具体的功能，判断是否为系统异常操作
  如果是异常操作导致，则忽略不计
  如果为系统正常操作导致
  则判断是否需要新增TEMP表空间（新增DBF文件）

方法1
新增TEMP表空间的步骤（并设置为自动扩展）

create temporary tablespace temp02 tempfile '//PATH/TEMP03.DBF' size 10M autoextend on;

1.4总结
表空间最大值为根据Block_size来确认的
查询Block_size的值（表空间数据文件容量与DB_BLOCK_SIZE有关）
4K=16GB
8K=32GB
16K=64GB
32K=128GB
64K=256GB
Block_size一般是不能修改，也不建议修改

先查询Block_size的设定

show parameters db_block_size;

即可根据查询值确认表空间最大值是多大

• 假设表空间未开启自动扩展
  ==>

 alter database tempfile 'filename' autoextend on next 100M

综上所述，为本次0RA-1652的Issue处理步骤