Results 1 to 3 of 3

Thread: Ora-01652

  1. #1
    Join Date
    Oct 2002
    Location
    Cochin, INDIA
    Posts
    9

    Unanswered: Ora-01652

    While importing a table, I am struck with the following error:

    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE INDEX "IDX_NAME" ON "TBL_NAME" ("COL_NAME" ) PCTFREE 10 "
    "INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 91381760 NEXT 409600 MINEXTENTS 1 M"
    "AXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
    "LT) LOGGING"
    IMP-00003: ORACLE error 1652 encountered
    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    Hope someone could provide me with a detailed answer on this.
    Thanking in advance
    Saju Joseph

  2. #2
    Join Date
    Nov 2002
    Posts
    833

    Re: Ora-01652

    Originally posted by peruvachira
    While importing a table, I am struck with the following error:

    IMP-00017: following statement failed with ORACLE error 1652:
    "CREATE INDEX "IDX_NAME" ON "TBL_NAME" ("COL_NAME" ) PCTFREE 10 "
    "INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 91381760 NEXT 409600 MINEXTENTS 1 M"
    "AXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
    "LT) LOGGING"
    IMP-00003: ORACLE error 1652 encountered
    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    Hope someone could provide me with a detailed answer on this.
    Thanking in advance
    Saju Joseph
    increase the tablespace temp
    or create the indexes with nologging and possibly parallel option

    or alter datafile <tempdatafile> autoextend on ...

    but may be the temp was fragmented and full with older stuff and an
    alter tablespace temp default storage (pctincrease 0); will free the spaces

    bernd

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Smile

    When building indexes, Oracle sorts the table data and that uses the user's default temporary tablespace. You got this error because Oracle couldn't find 128 bytes of contiguos space to allocate, and the DDL was canceled.

    You can add disk space to TEMP tablespace datafiles, drop the tablespaec and recreate it with autoextent 0, or create tablespace tbs datafile 'dbfname' sizeM temporary;

    Also is you save your index DDL to indexfile, you can run it later w/o to have to re-run the import.


    Hope that helps,

    clio_usa
    OCP - DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •