| Hi TEMP TABLESPACE Problem ( Challenging JOB) [message #58420] |
Mon, 25 August 2003 22:03  |
Hiren Patel
Messages: 10
Registered: May 2002
|
Junior Member |
|
|
Hi Friends
I would be thankfull to anyone who will solve Universal problem.My TEMP tablespace get growth in increasing order it sometimes get beyond 4500M,so we have to extend it,whenever user informs that they do not run thier reports,so we find out why they are not able to run thier reports came to know that TEMP tablespace requires more space.So we have to increase the size of tablespace.Is there anywayt to decrease the size of tablespace dynamically or any alternative solutions( NOTE IMP : I don't want my TEMP tablespace AUTOEXTEND ON)
|
|
|
|
| Re: Hi TEMP TABLESPACE Problem ( Challenging JOB) [message #58421 is a reply to message #58420] |
Mon, 25 August 2003 23:48  |
nimzs
Messages: 1
Registered: August 2003
|
Junior Member |
|
|
Whats the queries they are running and whats the data we are talking,there maybe cartesian products occuring in of the reports, We have around 10-15 gigs data have a temp table space of 1000 Mb and it suffices well unless there are some queries which are not written properly.
Provide us with some more details
Regards
|
|
|
|
| Re: Hi TEMP TABLESPACE Problem ( Challenging JOB) [message #58423 is a reply to message #58421] |
Tue, 26 August 2003 02:54 |
Mark
Messages: 284
Registered: July 1998
|
Senior Member |
|
|
Did you verify that the storage parameter for your temporary tablespace would be a multiple of
the istance parameter sort_area_size?
If is lower than that, you use much more space than needed.
If not think about increase sort_area_size.
Mark
|
|
|
|
|
|
| Re: Hi TEMP TABLESPACE Problem ( Challenging JOB) [message #58486 is a reply to message #58420] |
Mon, 01 September 2003 22:15 |
Hiren Patel
Messages: 10
Registered: May 2002
|
Junior Member |
|
|
Hi Friends
Some of the friend has given a solution but it does'nt work for me ,should i increase sort_area_size
I would be thankfull to anyone who will solve Universal problem.My TEMP tablespace get growth in increasing order it sometimes get beyond 4500M,so we have to extend it,whenever user informs that they do not run thier reports,so we find out why they are not able to run thier reports came to know that TEMP tablespace requires more space.So we have to increase the size of tablespace.Is there anywayt to decrease the size of tablespace dynamically or any alternative solutions( NOTE IMP : I don't want my TEMP tablespace AUTOEXTEND ON)
|
|
|
|
| Re: Hi TEMP TABLESPACE Problem ( Challenging JOB) [message #58530 is a reply to message #58420] |
Mon, 15 September 2003 14:12 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
For our data warehouse, we have a 9 GB Temp space. Depending on the type of queries, it sometimes uses upto 80-90 % of this.
Couple of things about TEMP
1. it is of type 'temporary' so whenever the DB is recycled, it is set to 0 bytes used.
2. Tune your SQL so as to get max efficient result. Some bad queries with 5-6 Full table scans have indeed filled up this Temp.
3. Increase you sort_area_size to 1MB or even 2MB and make the initial and next of TEMP the same as the sort_area_size and set the pctincrease to 0.
4. I have set the sort_area_retain_size to 0, which has worked for me.
5. If you want to clean up the temp space, use this SQL: (double check in DBA studio)
alter tablespace temp default storage (pctincrease 0);
6. Sell to your superior that big queries DOES need big Temp space, specially when there is SORT involved. Get more space.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
