none
ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

    Question

  • I have huge table in oracle as my Data source and I am extracting data to Sql server. All my source tables have 100 ~ 200 columns.

     

    I have built my package with various different tasks, and in my OLE DB source has a connection string to Oracle. The query generates around 400 records.

     

    When I click on preview button I get the following error

     

    ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

     

    Please let me know the options to address/fix.
    Wednesday, April 21, 2010 12:26 AM

Answers

  • Hello Kori,

    The query above determines the number of blocks free. not the number of extents.

    An extent consists of n blocks.

    by default a block consists of 8192B (8Kb). On our configuration one extent consists of 1MB which means 128 Blocks. However, this is something that you can define on your installation. So check your setup to determine which is the size of an extent.

    If the size of your extend is 1MB than an extention of 128 extents in your table space requires 128*128 blocks = 16384

    Hope this helps

     

     

    Thursday, April 22, 2010 7:51 AM

All replies

  • Hello Kori,

    It seems that the files associated to the tablespace PSAPTEMP are full... 

    check out this query:

    select sum(free_blocks)
    from gv$sort_segment
    where tablespace_name = '<TEMP TABLESPACE NAME>'

    0 as result means no free space available 

    You have two possibilities: either extend your existing datafile, in that case run following query:

    ALTER DATABASE DATAFILE ‘D:\ORACLEXE\ORADATA\XE\USERS.DBF‘ RESIZE 200M

    Or you can add additional files to your tablespace:

    ALTER TABLESPACE 'USERS' ADD DATAFILE ‘D:\ORACLEXE\ORADATA\XE\USERS_2.DBF‘ SIZE 50M

    Make sure to run this queries with sufficient rights. Refer to your DBA if any doubts

     

    The paths and tabelspace names are provided as such and have to be adapted to your situation...

    Kind regards

     

    Wednesday, April 21, 2010 11:38 AM
  • When I execute the above query I find 5632 free blocks. So does it infer there is sufficient tempspace?

    Wednesday, April 21, 2010 2:30 PM
  • Please find the detailed error message herebelow

    Error: 0xC0202009 at Data Flow Task, OLE DB Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

    An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP".

    Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

    Wednesday, April 21, 2010 2:31 PM
  • Also When I execute the following query

                   select tablespace_name,next_extent,max_extents from dba_tablespaces where 
                   tablespace_name='PSAPTEMP';
     
                   The out put was 
                   TABLESPACE_NAME                NEXT_EXTENT MAX_EXTENTS
                   ------------------------------        -----------       -----------
                   PSAPTEMP                                   1048576
     
    Wednesday, April 21, 2010 2:36 PM
  • Hello Kori,

    The query above determines the number of blocks free. not the number of extents.

    An extent consists of n blocks.

    by default a block consists of 8192B (8Kb). On our configuration one extent consists of 1MB which means 128 Blocks. However, this is something that you can define on your installation. So check your setup to determine which is the size of an extent.

    If the size of your extend is 1MB than an extention of 128 extents in your table space requires 128*128 blocks = 16384

    Hope this helps

     

     

    Thursday, April 22, 2010 7:51 AM