SSIS and Oracle: challenge of ‘Unable to extend temp segment’ in Oracle

If SSIS is being used to retrieve large amounts of data from an Oracle database, the SSIS package may fail with the following error message:
ORA-01652: unable to extend temp segment by 256 in tablespace
This error basically means that Oracle could not extend the temporary segment for the required number of blocks in the user’s allocated tablespace.  There are a few solutions to this issue:
  • Increase the users temp tablespace
  • Although it’s an Oracle error, it may show up structural design issues with the package itself. Thus, the SSIS package could be changed so that, instead of one large query, the Oracle database is serving up smaller chunks of data at a time. This may mean ‘daisy-chaining’ a number of components, who each serve up smaller parts of the larger query that was initially being executed.
As always, it’s best to have a chat with your friendly Oracle dba owner of the source database for their thoughts.

Add to Technorati Favorites

Leave a Reply