关于ORA-1652错误，可谓是太常见不过了，但是我想未必每个人都能知道其根本原因和其处理方式，
今天在群里有人问到这个问题，故就有了这篇文章。
该错误的格式如下（摘取该网友的错误信息）：
ORA-1652: unable to extend temp segment by 128 in tablespace OCSTS
我们先来看看MOS文档对此错误的描述（其实通过oerr 1652也能得到）：
Error: ORA-1652
Text: unable to extend temp segment by %s in tablespace %s
------- -----------------------------------------------------------------------
Cause: Failed to allocate an extent for temp segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated or create the object in another
tablespace.
简单的描述该错误实质就是oracle在该tablespace上无法分配一段连续的extent了进而抛出错误。
在该种情况下，我们首先需要去查询表空间使用率，如下：
SQL> select *
2 from (Select a.tablespace_name,
3 to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
4 to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
5 to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
6 '99,999.999') use_bytes,
7 to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
8 from (select tablespace_name, sum(bytes) bytes
9 from dba_data_files
10 group by tablespace_name) a,
11 (select tablespace_name, sum(bytes) bytes
12 from dba_free_space
13 group by tablespace_name) b
14 where a.tablespace_name = b.tablespace_name
15 union all
16 select c.tablespace_name,
17 to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
18 to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
19 to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
20 to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
21 from (select tablespace_name, sum(bytes) bytes
22 from dba_temp_files
23 group by tablespace_name) c,
24 (select tablespace_name, sum(bytes_cached) bytes_used
25 from v$temp_extent_pool
26 group by tablespace_name) d
27 where c.tablespace_name = d.tablespace_name)
28 order by tablespace_name
29 /
TABLESPACE TOTAL_BYTES FREE_BYTES USE_BYTES USE
---------- ----------- ----------- ----------- -------
ROGER 50.000 49.938 0.063 0.13%
SYSAUX 250.000 6.875 243.125 97.25%
SYSTEM 480.000 5.188 474.813 98.92%
TEMP 20.000 2.000 18.000 90.00%
UNDOTBS1 25.000 5.250 19.750 79.00%
USERS 5.000 4.563 0.438 8.75%
6 rows selected.
这时通常的情况下是关闭了datafile的自动扩容的，当然为了解决这个问题，你可以开启自动扩容；
不过, 通常我们并不推荐这么做，而是通过如下方式进行处理：
SQL> col file_name for a60
SQL> select file_id,file_name,bytes/1024/1024
2 from dba_temp_files
3 where tablespace_name='TEMP';
FILE_ID FILE_NAME BYTES/1024/1024
---------- ------------------------------------------------------------ ---------------
1 F:\ORACLE_TEST\PRODUCT\10.2.0\ORADATA\ROGER\TEMP01.DBF 20
SQL> select username,default_tablespace,TEMPORARY_TABLESPACE
2 from dba_users
3 where username='ROGER';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ -----------------------
ROGER ROGER TEMP
SQL> alter database datafile 5 resize 1024m;
Database altered.
SQL> select file_id,bytes/1024/1024,autoextensible
2 from dba_data_files
3 where tablespace_name='ROGER';
FILE_ID BYTES/1024/1024 AUTOEXTENSIBLE
------- --------------- --------------
5 1024 NO
#### 或者我们想办法去优化我们的sql语句，尽量降低排序操作。####
SQL> conn roger/roger
Connected.
SQL> create table ht1 as select * from sys.dba_objects where 1=2;
Table created.
SQL> begin
2 for i in 1..100 loop
3 insert /*+ append */into ht1 select * from sys.dba_objects;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from ht1;
COUNT(*)
----------
5003900
SQL> begin
2 for i in 1 .. 100 loop
3 insert /*+ append */
4 into ht1
5 select * from ht1;
6 commit;
7 end loop;
8 end;
9 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table ROGER.HT1 by 1024 in tablespace ROGER
ORA-06512: at line 3
#### 下面为该表创建index试试 ####
SQL> create index idx_ht1_id on ht1(owner,object_id) tablespace roger;
create index idx_ht1_id on ht1(owner,object_id) tablespace roger
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
++++++ 在另外的窗口中查询，当前是谁在使用temp。++++++
SQL> set lines 140
SQL> col username for a10
SQL> col osuser for a15
SQL> col sql_text for a70
SQL> col tablespace_name for a10
SQL> SELECT a.username,
2 a.sid,
3 a.serial#,
4 a.osuser,
5 b.tablespace,
6 b.blocks,
7 c.sql_text
8 FROM v$session a, v$tempseg_usage b, v$sqlarea c
9 WHERE a.saddr = b.session_addr
10 AND c.address = a.sql_address
11 AND c.hash_value = a.sql_hash_value
12 ORDER BY b.tablespace, b.blocks
13 /
no rows selected
SQL> /
USERNAME SID SERIAL# OSUSER TABLESPACE BLOCKS SQL_TEXT
-------- ---- ------- --------------- ----------- ------ ----------------------------------------------------------------
ROGER 147 3 KILLDB\think TEMP 2432 create index idx_ht1_id on ht1(owner,object_id) tablespace roger
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
TABLESPACE TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------- ------------ ----------- -----------
TEMP 2432 0 2432
SQL> /
TABLESPACE TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------- ------------ ----------- -----------
TEMP 2432 1920 512
从上面的测试我们可以看到，create index是需要进行排序操作的，如果segment很大，
那么很可能需要非常大的temp segment。
到这里我们有必要简单的总结下，哪些情况下需要使用temp segment呢？
总的来说有如下几种情况需要temp段：
-- 排序操作 比如select或dml(ddl)语句中包含order by之类;
-- create index
-- create pk constraint (其实这个跟create index类似，因为创建主键约束时默认会同时创建index)
-- enable constraint操作
-- create table语句
既然我们知道了哪些情况下的操作是需要temp segment的，那么这时我就萌生出一个想法了：
能否估算某个操作大概需要多少temp segment呢？那样的话就可以预先进行操作，防止该错误发生了。
SQL> analyze table ht1 compute statistics;
Table analyzed.
SQL> select count(*) from ht1;
COUNT(*)
----------
5003900
SQL> select table_name,column_name,avg_col_len
2 from user_tab_columns
3 where table_name='HT1';
TABLE_NAME COLUMN_NAME AVG_COL_LEN
------------------------------ ------------------------------ -----------
HT1 OWNER 5
HT1 OBJECT_NAME 24
HT1 SUBOBJECT_NAME 2
HT1 OBJECT_ID 4
HT1 DATA_OBJECT_ID 2
HT1 OBJECT_TYPE 8
HT1 CREATED 7
HT1 LAST_DDL_TIME 7
HT1 TIMESTAMP 19
HT1 STATUS 5
HT1 TEMPORARY 1
HT1 GENERATED 1
HT1 SECONDARY 1
13 rows selected.
###### 通过explain plan方式来看排序需要多少temp ######
SQL> explain plan for
2 create index idx_ht1_id on ht1(owner,object_id) tablespace roger;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 4061622788
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 5003K| 42M| 18132 (1)| 00:03:38 |
| 1 | INDEX BUILD NON UNIQUE| IDX_HT1_ID | | | | |
| 2 | SORT CREATE INDEX | | 5003K| 42M| | |
| 3 | TABLE ACCESS FULL | HT1 | 5003K| 42M| 15239 (1)| 00:03:03 |
-------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Note
-----
- estimated index size: 117M bytes
14 rows selected.
从上面可以看出该操作进行排序操作需要42m空间，具体这是如何计算出来的，不得而知，不过我可以进行
猜测，oracle必定也是根据平均列长度等进行计算出来的，有兴趣的朋友可以研究研究。
参与排序的数据源包括所有要索引的列和rowid,列的长度，还有index entry header。
http://dbaoracle.itpub.net/post/901/29560 提到了，但是也不确认具体每部分到底是多少byte。
由于现在使用的win7，不方便进行有些实验，关于该部分回头我会再写一篇，进行描述rowid，列长度
以及index entry header分别占据多少个byte。
mos文档 ORA-01652: Estimate Space Needed to CREATE INDEX [ID 100492.1] 中也提到了如何进行估算。
但是很可惜，该文档描述太过含糊了，比如说如果我有2个或2个以上的列需要进行索引，那么计算的时候根据
那个列为准呢？难道是最大的列平均长度？
如果我这里使用较大的列来计算，那么就是owner列，如下：
SQL> select 5003900*5/1024/1024 from dual;
5003900*5/1024/1024
-------------------
23.8604546
该结果进行四舍五入即为24M，再乘以3，那么即为72M，远大于执行计划中的42M。
当然为什么说这里是乘3而不是2或4呢？我猜测oracle这里也是在往大的方向进行估算，毕竟大点比小好。
最后，至于说到底如何计算才是准为准确的？我想这个问题不必那么较真了，毕竟无多大意义，只要能
让我们的操作顺利进行就行了。
如果大家有兴趣，可以去看看如下几个文档：
ORA-1652 Error Troubleshooting [ID 793380.1]
How Do You Find Who And What SQL Is Using Temp Segments [ID 317441.1]
OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s [ID 19047.1]
ORA-01652: Estimate Space Needed to CREATE INDEX [ID 100492.1]
Extent Sizes for Sort, Direct Load and Parallel Operations (PCTAS & PDML) [ID 50592.1]
How Can Temporary Segment Usage Be Monitored Over Time? [ID 364417.1]
Temporary Segments Are Not Being De-Allocated After a Sort [ID 1039341.6]
回复 老熊 取消回复