unable to extend temp segment by 128 in tablespace TEMP2
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 67
- Joined: Thu Apr 06, 2006 7:13 am
unable to extend temp segment by 128 in tablespace TEMP2
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.
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.
Bhaskar Jha
-
- Participant
- Posts: 67
- Joined: Thu Apr 06, 2006 7:13 am
-
- Participant
- Posts: 67
- Joined: Thu Apr 06, 2006 7:13 am
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.bhaskarjha wrote:Can you please advise some tuning tips? I have already created index on the table on which view is created. Thanks.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 67
- Joined: Thu Apr 06, 2006 7:13 am