Home » RDBMS Server » Server Administration » TEMPORARY TABLESPACE UTILIZATION TREND (Oracle Enterprise, 11.2.0.3, AIX 6.1)
TEMPORARY TABLESPACE UTILIZATION TREND [message #681896] Mon, 07 September 2020 12:20 Go to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Hi All,

We have ETL/reporting application which is supported by the same Oracle DB. At times, we face temp space error and either of the jobs get failed. Hence we have created 2 temporary tablespaces and segregated the DB accounts to have default temporary tablespace to narrow down the issue.

We would like to know the historic utilization/growth for each temporary tablespace. For example, what was the highest utilization for the temporary tablespace each day? Did it hit 90%,50% or less than that. Went through various documentations to find the query which assists in getting the current temp utilization details.

How do we get the historic report on temporary tablespace utilization when we have multiple tablespaces?

Regards,
Antony
Re: TEMPORARY TABLESPACE UTILIZATION TREND [message #681897 is a reply to message #681896] Mon, 07 September 2020 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no history of temp space usage, you have to build your own regularly querying v$sort_usage.

Re: TEMPORARY TABLESPACE UTILIZATION TREND [message #681898 is a reply to message #681897] Mon, 07 September 2020 14:22 Go to previous messageGo to next message
jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai
Senior Member
Thanks Michel. Wondering if I can leverage anything that already exists with the product.
Re: TEMPORARY TABLESPACE UTILIZATION TREND [message #681899 is a reply to message #681898] Mon, 07 September 2020 14:46 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A better choice, for your case, maybe v$sort_segment or its aggregating view DBA_TEMP_FREE_SPACE (still building you own history table from it).

Previous Topic: Temp Tablespace Issue somehow fixed SQL advisor
Next Topic: Oracle12c Blocking Session Information
Goto Forum:
  


Current Time: Thu Mar 28 07:57:21 CDT 2024