Check and identify cause of previous temp tablespace usage
822778Dec 7 2012 — edited Dec 7 2012Hi
Our production ERP database is on solaris and on version 9.2.0.8.0. The application tier and database tier are on two separate nodes.
Recently we observed that the temporary tablespace is being consumed more on a particular day. While monitoring the database,we observed the free temp tablespace was less and hence added 10gb space to it. However within 5-6 hours this space was utilised and certain requests/jobs failed due to no space in temp. This happened on 04th December 2012. Though the situation returned to normal post that,we need to find the cause of consumption of temporary tablespace in such large volumes. We are on 9i database and hence tried identifying the queries through statspack report as no views in particular exist for 9i.
We came across many queries which would help us identfy the current temp usage,but in 9i,we didnot find anything which would guide us on the historic temp tablespace usage. Can it be found via statspack? If yes,what exactly to check for in it? Request you to all please advice. Thanks.
Regards
Rdxdba