数据库|mysql教程
ORA-01652,ORA-01652: unable to
数据库-mysql教程
asp记账系统源码,deb文件ubuntu,tomcat服务器多开,影院票价爬虫,php直播室源码下载,Fa1Seo是什么意思lzw
最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错误提示。这个是个比较常见的错误。索引在
在线认证系统源码,vscode中全局搜索,ubuntu 选中 删除,tomcat启动ssm工程,爬虫增色,cps php,seo是指什么营销,网站源码主页怎么设置,源码之家easyui后台模板lzw
38卡盟源码,VScode使用vue和js,ubuntu退出编程,tomcat放行网页权限,实名认证爬虫,php获取option,江北新区seo优化服务,中英文企业网站系统源代码,dedecms织梦手机站模板lzw
最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错误提示。这个是个比较常见的错误。索引在创建的时候需要使用到该用户缺省的临时表空间进行排序，以及在索引表空间生成临时段。如果当前的索引表空间限制了自动扩展或者已经达到了数据文件的最大值，此错误提示便会出现。下面是具体的分析及其解决过程。
推荐阅读：
ORA-01172、ORA-01151错误处理
ORA-00600 [2662]错误解决
ORA-01078 和 LRM-00109 报错解决方法
ORA-00471 处理方法笔记
ORA-00314,redolog 损坏，或丢失处理方法
ORA-00257 归档日志过大导致无法存储的解决办法
1、错误提示信息
alter index err ORA-01652: unable to extend temp segment by 8192 in tablespace
GX_ARCHIVE_IDX
DECLARE
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX
ORA-06512: at line 90
#下面的信息来自alert log
Sun Mar 30 03:08:51
ORA-1652: unable to extend temp segment by 128 in tablespace GX_ARCHIVE_IDX
ORA-1652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX
#故障环境
SQL> select * from v$version where rownum<2;
BANNER
—————————————————————-
Oracle Database 10g Release 10.2.0.3.0 – 64bit Production
SQL> ho cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP4 (x86_64) – Kernel \r (\l).
2、关于ORA-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.
*** Important: The notes below are for experienced users – See Note:22080.1
Explanation:
This error is fairly self explanatory – we cannot get enough space for a temporary segment.
The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.
NOTE: A “temp segment” is not necessarily a SORT segment in a temporary tablespace.
It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.
eg: When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data.
This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation.
It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend
it report ORA-1652 rather than an INDEX related space error.
临时段被使用的情形
A TEMPORARY segment may be from:
1) A SORT Used for a SELECT or for DML/DDL
2) CREATE INDEXThe index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX tablespace.
Once the index build is complete the segment type is changed.
3) CREATE PK CONSTRAINT
4) ENABLE CONSTRAINT
5) CREATE TABLE New tables start out as TEMPORARY segments.
Eg: If MINEXTENTS is > 1 or you issue CREATE table as SELECT.
6) Accessing a GLOBAL TEMPORARY TABLE When you access a global temporary table a TEMP segment is instantiated to hold the temporary data.
3、TROUBLESHOOTING ORA-01652(Reference Doc ID 1267351.1)
#下面是无法扩展临时段的2种情形
EXAMPLE 1:
Temporary tablespace TEMP is being used and is 50gb in size (a recommended minimum for 11g)
TIME 1 : Session 1 starts a long running query
TIME 2 : Session 2 starts a query and at this point in time Session 1 has consumed 48gb of TEMP’s free space
TIME 3 : Session 1 and Session 2 receive an ORA-1652 because the tablespace has exhausted of of its free space
Both sessions fail .. and all temp space used by the sessions are freed (the segments used are marked FREE for reuse)
TIME 4 : SMON cleans up the temporary segments used by Session 1 and Session 2 (deallocates the storage)
TIME 5 : Queries are run against the views V$SORTSEG_USAGE or V$TEMSEG_USAGE and V$SORT_SEGMENT … and it is found that no space is being used (this is normal)
EXAMPLE 2:
Permanent tablespace INDEX_TBS is being used and has 20gb of space free #此时无法扩展临时表空间的问题当属第2种情形
