unable to extend temp segment by 8192 in tablespace MSCX

Issue Development team send email with given below issue. Concurrent program failed with given error. ---------------------------------------------------------- <> <>CREATE INDEX ITEM_CATEGORIES_N3_IKN ON ITEM_CATEGORIES_IKN(CATEGORY_SET_ID,ORGANIZATION_ID,SR_INSTANCE_ID,SR_CATEGORY_ID,CATEGORY_NAME,DESCRIPTION) PARALLEL TABLESPACE MSCX STORAGE( INITIAL 106496 NEXT 67108864 PCTINCREASE 0) PCTFREE 10 INITRANS 11 MAXTRANS 255 ORA-12801: error signaled in parallel query server P005, instance ios0901e:EPRODR1 (1) ORA-01652: unable to extend temp segment by 8192 in tablespace MSCX <> <>ALTER TABLE MSC_ITEM_CATEGORIES EXCHANGE PARTITION ITEM_CATEGORIES__1 WITH TABLE ITEM_CATEGORIES_IKN INCLUDING INDEXES ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION Calling Linking of Sales Order for 11i source ... Procedure LINK_SUPP_SO_DEMAND_EXT started. Truncated Source AD Tables Successfully ---------------------------------------------------------- Troubleshooting Steps Script was failing while creating index. I check initial and next space utilization of tablespace, which was good. I checked space utilization using given below query. #CONTIG_FREE_SPACE.sql #PL/SQL script to determine how much contiguous #free space is available for each tablespace in the database. Oracle will #acquire space by searching first for exact fit, then next best fit, and lastly#coalesce if possible. create table SPACE_TEMP ( TABLESPACE_NAME CHAR(30), CONTIGUOUS_BYTES NUMBER) / declare cursor query is select * from dba_free_space WHERE TABLESPACE_NAME='MSCX' order by tablespace_name, block_id; this_row query%rowtype; previous_row query%rowtype; total number; begin open query; fetch query into this_row; previous_row := this_row; total := previous_row.bytes; loop fetch query into this_row; exit when query%notfound; if this_row.block_id = previous_row.block_id + previous_row.blocks then total := total + previous_row.bytes; insert into SPACE_TEMP (tablespace_name) values (previous_row.tablespace_name); else insert into SPACE_TEMP values (previous_row.tablespace_name, total); total := this_row.bytes; end if; previous_row := this_row; end loop; insert into SPACE_TEMP values (previous_row.tablespace_name, total); end; / set pagesize 60 set newpage 0 set echo off ttitle center 'Contiguous Extents Report' - skip 1 center new_today skip 3 break on "TABLESPACE NAME" skip page duplicate spool contig_free_space.lis rem column "CONTIGUOUS BYTES" format 999,999,999 column "COUNT" format 999 column "TOTAL BYTES" format 999,999,999 column "TODAY" noprint new_value new_today format a1 rem select TABLESPACE_NAME "TABLESPACE NAME", CONTIGUOUS_BYTES "CONTIGUOUS BYTES", count(*) "COUNT", CONTIGUOUS_BYTES*count(*) "TOTAL BYTES", to_char(sysdate,'FMMonth DD, YYYY') "TODAY "from SPACE_TEMP where CONTIGUOUS_BYTES is not nullgroup by TABLESPACE_NAME, CONTIGUOUS_BYTES order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc / spool off drop table SPACE_TEMP / Solution We dropped recyle bin object from tablespace to free space. SQL> purge tablespace Again I checked contigouse space which was more than required next space. Note--> Next space allocation is in form of next value (its not number of block. It is number of byte)