关于ORA-1652的一点简单总结
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 关于ORA-1652的一点简单总结
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
关于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了进而抛出错误。 在该种情况下，我们首先需要去查询表空间使用率，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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. |
|
1 2 |
这时通常的情况下是关闭了datafile的自动扩容的，当然为了解决这个问题，你可以开启自动扩容； 不过, 通常我们并不推荐这么做，而是通过如下方式进行处理： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
从上面的测试我们可以看到，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呢？那样的话就可以预先进行操作，防止该错误发生了。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
从上面可以看出该操作进行排序操作需要42m空间，具体这是如何计算出来的，不得而知，不过我可以进行 猜测，oracle必定也是根据平均列长度等进行计算出来的，有兴趣的朋友可以研究研究。 参与排序的数据源包括所有要索引的列和rowid,列的长度，还有index entry header。 <a href="http://dbaoracle.itpub.net/post/901/29560" style="font-size: 12px; font-family: monospace;">http://dbaoracle.itpub.net/post/901/29560</a> 提到了，但是也不确认具体每部分到底是多少byte。 由于现在使用的win7，不方便进行有些实验，关于该部分回头我会再写一篇，进行描述rowid，列长度 以及index entry header分别占据多少个byte。 mos文档 ORA-01652: Estimate Space Needed to CREATE INDEX [ID 100492.1] 中也提到了如何进行估算。 但是很可惜，该文档描述太过含糊了，比如说如果我有2个或2个以上的列需要进行索引，那么计算的时候根据 那个列为准呢？难道是最大的列平均长度？ 如果我这里使用较大的列来计算，那么就是owner列，如下： |
|
1 2 3 4 5 |
SQL> select 5003900*5/1024/1024 from dual; 5003900*5/1024/1024 ------------------- 23.8604546 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
该结果进行四舍五入即为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] |
10 Responses to “关于ORA-1652的一点简单总结”
大家可以参考下tom精辟回答 关于ora-1652 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:36859040165792
你这个查询表空间利用率的脚本存在BUG，比如某个表空间完全耗尽时，dba_free_space里是没有这个表空间的数据的，这样关联出来就会丢失这个表空间的信息。应该使用外关联。
to 熊哥：这个到没注意过 只是知道有几个关于dba_free_space的bug。 10204+ 还有这个问题？ 你指的bug有具体的bug号没 ？
Freespace=0 in the Repository Admin Utility but tablespace does have freespace [ID 133758.1]
——————————————————————————–
修改时间 16-FEB-2011 类型 PROBLEM 状态 PUBLISHED
*** Checked for relevance on 16-Feb-2011 ***
Problem Description
——————-
In the RAU one or more Tablespaces show 0 for freespace. When you go into
SQLPLUS with the select statement:
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space
group by tablespace_name;
Shows lots of space for that tablespace
Solution Description
——————–
Regrant all of the permissions in the install manual for the repository owner.
GRANT EXECUTE ON DBMS_LOCK TO ;
GRANT EXECUTE ON DBMS_PIPE TO ;
GRANT CREATE TABLE TO ;
GRANT CREATE VIEW TO ;
GRANT CREATE PROCEDURE TO ;
GRANT CREATE SYNONYM TO ;
GRANT CREATE SEQUENCE TO ;
GRANT SELECT ON sys.v_$nls_parameters TO WITH GRANT OPTION;
GRANT SELECT on sys.V_$PARAMETER TO ;
GRANT SELECT ON dba_rollback_segs TO ;
GRANT SELECT ON dba_segments TO ;
GRANT CREATE ANY SYNONYM TO ;
GRANT DROP ANY SYNONYM TO ;
GRANT CREATE PUBLIC SYNONYM TO ;
GRANT DROP PUBLIC SYNONYM TO ;
GRANT ck_oracle_repos_owner to ;
GRANT CONNECT, RESOURCE TO ;
Explanation
———–
Designer requires all permissions that are given in the install manual to
operate properly.
不是指Oracle的BUG，我的意思是你的脚本有缺陷。比如一个表空间的所有空间都已经用完的时候，dba_free_space里面就不会有这个表空间的记录了。
to 熊哥： 哈哈 了解了。。。。
[…] http://www.killdb.com/2011/09/30/%e5%85%b3%e4%ba%8eora-1652%e7%9a%84%e4%b8%80%e7%82%b9%e7%ae%80%e5%8… […]
参考这个表空间使用率 查询脚本：
http://www.oracledatabase12g.com/archives/script-tablespace-report.html
to ml： 3Q
I have been browsing on-line more than 3 hours these days, yet I never discovered any attention-grabbing article like yours. It’s lovely value sufficient for me. In my view, if all web owners and bloggers made just right content material as you probably did, the web will likely be much more helpful than ever before.
Leave a Reply
You must be logged in to post a comment.