0

I want to run a query which takes a lot of temp space. However, I don't want the query to exhaust all the available temp space. How can this be achieved?

Arc
  • 1,680
  • 6
  • 30
  • 57
  • add query to question, its helps a lot. – Michael Pakhantsov Nov 30 '10 at 13:48
  • It is a normal problem and it failed with temp space problem in a hash join. However, we can't let the query use the complete temp space. It throws ORA-12805 this time round. The query had been working fine for months. – Arc Nov 30 '10 at 13:53
  • 2
    I've often found that any query that causes the database to run out of temp space is either a bad query (e.g. missing a join condition) or is the result of a execution bad plan caused by stale statistics, especially if it was working fine. Although this might also be because the DB I'm working on has a *lot* of temp space. Explain plan can tell you how much temp space is expected to be used by query. – Mike Meyers Nov 30 '10 at 17:03

2 Answers2

1

You can add a resource plan that limits it.

erbsock
  • 1,207
  • 8
  • 10
  • http://www.databasejournal.com/features/oracle/article.php/3296961/Oracle-Database-Resource-Manager-Part-1Overview.htm – erbsock Nov 30 '10 at 15:36
1

You can also create another temporary tablespace with a fixed size and assign the user running the query to that one.

DCookie
  • 42,630
  • 11
  • 83
  • 92