Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: November 05, 2016 - 5:00 am UTC

Last updated: November 09, 2016 - 2:08 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

HOW CAN I DETERMINE THAT WHAT SHOULD BE THE SIZE OF TEMPORARY TABLESPACE FOR ORACLE DATABASE?


WHAT ARE THE MEASURES TO BE CONSIDERED FOR SIZING TEMPORARY TABLESPACE?

HOW SHOULD I RESIZE THE TEMPORARY TABLESPACE SO THAT WE DO NOT ENCOUNTER ERROR IN FUTURE REGARDING TEMPORARY TABLESPACE SIZE?

and Connor said...

You could look at AWR reports and/or V$SQL_WORKAREA_ACTIVE to get an idea of what typical usage is, but of course, it only takes 1 rogue query to burn lots of temporary tablespace, so that easiest way is probably to make your temporary tablespace autoextend, so that it can grow to whatever is needed by your database.

Also, check out resumable operations which let you "pause" a statement when it runs out of temporary tablespace, rather than error. You can then (as DBA) react and decide whether you want to kill the query or allocate more space to the temporary tablespace.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Link

A reader, November 07, 2016 - 5:54 pm UTC

Also, check out resumable operations which let you "pause" a statement when it runs out of temporary tablespace, rather than error. You can then (as DBA) react and decide whether you want to kill the query or allocate more space to the temporary tablespace.

Do you have link or explanation to do so?

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.