Search This Blog

Thursday, June 6, 2013

ORA-01630: max # extents (505) reached in temp segment in tablespace

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
-- 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.


Ref : TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors [ID 1025288.6]

No comments:

Post a Comment

Transportable tablespace refresh

  1.check tablespace for the user which need to refresh -------------------------------------------------------------------  SQL> select ...