I got ORA-01630: max # extents (505) reached in temp segment in tablespace while creating the index.
SQL> show user
USER is "APPS"
SQL> create index xyz.xygts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id);
create index xyz.xygts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id)
*
ERROR at line 1:
ORA-01630: max # extents (505) reached in temp segment in tablespace
XY_DATA
SQL> select BYTES/1024/1024 from dba_segments where SEGMENT_NAME='MTL_MATERIAL_TRANSACTIONS';
BYTES/1024/1024
---------------
10510
SQL> show user
USER is "APPS"
SQL> create index xyz.xygts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id);
create index xyz.xygts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id)
*
ERROR at line 1:
ORA-01630: max # extents (505) reached in temp segment in tablespace
XY_DATA
SQL> select BYTES/1024/1024 from dba_segments where SEGMENT_NAME='MTL_MATERIAL_TRANSACTIONS';
BYTES/1024/1024
---------------
10510
-- Verified the pctincrease value
SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces where TABLESPACE_NAME='XYZ_DATA';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------------------ -------------- ----------- -----------
PCT_INCREASE
------------
XYZ_DATA 40960 40960 505
0
SQL> ALTER TABLESPACE XYZ_DATA DEFAULT STORAGE (pctincrease 50);
Tablespace altered.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces where TABLESPACE_NAME='XYZ_DATA';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------------------ -------------- ----------- -----------
PCT_INCREASE
------------
XYZ_DATA 40960 40960 505
50
-- I able to create index after increasing the pctincrease to 50
SQL> conn apps
Enter password:
Connected.
SQL> create index xyz.xxgts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id);
Index created.
Tablespace altered.
SQL> select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces where TABLESPACE_NAME='XYZ_DATA';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------------------ -------------- ----------- -----------
PCT_INCREASE
------------
XYZ_DATA 40960 40960 505
50
-- I able to create index after increasing the pctincrease to 50
SQL> conn apps
Enter password:
Connected.
SQL> create index xyz.xxgts_mtl_trx_source_idx on mtl_material_transactions(trx_source_line_id);
Index created.
Ref : TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors [ID 1025288.6]
No comments:
Post a Comment