sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-1652产生原因和MOS官方解决办法

时间:2016-11-20 14:54   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库出现报错ORA-1652,结合MOS官方文档详细统计出关于ORA-1652报错的几种产生原因和解决办法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 关于ORA-1652的一点简单总结

关于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]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-1652产生原因和MOS官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1298.html

Oracle研究中心

关键词:

通过EXPLAIN plan方式来看排序需要多少temp

ORA-1652解决办法

Oracle报错ORA-1652笔记

ORA-1652:unable to extend temp segment by