unable to extend temp segment by 128 in tablespace TEMP2

bhaskarjha · Post by **bhaskarjha** » Tue May 30, 2006 6:03 am

Hi,
I am facing problem while extracting data from a view to a table through ETL. The view contains huge amount of data. The error which i am getting is "unable to extend temp segment by 128 in tablespace TEMP2". I have reported the same to DBA here & he had increased the memory of tablespace but still I am getting the same error.
Pls help.

chulett · Post by **chulett** » Tue May 30, 2006 6:29 am

How can we help? It's a database problem.

Talk to your DBA. Have them increase the tablespace size. Again.

Either that or get some help tuning the view so it doesn't require so much temp space.

bhaskarjha · Post by **bhaskarjha** » Tue May 30, 2006 6:31 am

Can you please advise some tuning tips? I have already created index on the table on which view is created. Thanks.

kumar_s · Post by **kumar_s** » Tue May 30, 2006 6:33 am

Are you able to access and view the data from the View from command prompt.

kumar_s · Post by **kumar_s** » Tue May 30, 2006 6:36 am

And by the way, creating index will eat up more table space.

bhaskarjha · Post by **bhaskarjha** » Tue May 30, 2006 6:39 am

Hello Kumar,
I am not able to view data by command line prompt also. There also I am getting the same error. Should I remove the index & then try?

chulett · Post by **chulett** » Tue May 30, 2006 6:40 am

bhaskarjha wrote:Can you please advise some tuning tips? I have already created index on the table on which view is created. Thanks.

No, not really, other than extremely generic stuff. Include the topic in your conversation with your DBA. Run an explain plan. Look for Bad Things that would cause operations to spill over to physical disk. Ask the DBA about the possibility of increasing the 'sort' and/or 'hash' area sizes in the SGA so it doesn't need temp disk so much.

Or just throw more TEMP2 space into the pot, he can always take it back later.

Kumar - but if done properly it can cut down on the amount of TEMP space required during execution of the query, which is the specific problem here.

kumar_s · Post by **kumar_s** » Tue May 30, 2006 6:50 am

Got your point Craig :D

kcbland · Post by **kcbland** » Tue May 30, 2006 7:06 am

The view must be forcing the entire result set to resolve before streaming data, indications of this are use of GROUP BY in thedata. A SELECT * from the view must be more work than your current configuration can handle.

If you can't increase the TEMP space any more, than consider extracting subsets of the results data, allowing the database to resolve smaller sets of data. Ranged queries work here, or choose an integer based column and use the MOD technique to partition the data.

bhaskarjha · Post by **bhaskarjha** » Tue May 30, 2006 8:42 am

Thanks a lot to all of you for your suggestion. The job is working fine now. We have allocated more space to tablespace, restarted the database & used partitioned table.
Thanks again