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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s