Manage Learn to apply best practices and optimize your operations.

Managing temporary tablespace growth in Oracle

An Oracle user asks Database Backup and Recovery expert Brian Peasland about managing temporary tablespace growth.

I have 10g databases. When I went to tune the database I saw that the temporary tablespace was 38gb, but it was 1gb at the time of creation. Then I drop the temp tablespace and assign the database to a new temporary tablespace. What is the possibility to grow the temporary tablespace?
Since you are using Oracle 10g, you should probably have set the PGA_AGGREGATE_TARGET parameter. To accurately set this parameter, refer to the white paper titled "Tuning PGA in Oracle 9i" on my website. It is important that this parameter is set correctly. When performing sort operations, hash joins, and a few other operations, most of the work will be performed in memory. The PGA_AGGREGATE_TARGET parameter controls how much memory is available for these operations. For work that is too big to fit into memory, the temporary tablespace is used to store pieces of the work. If you do not adequately size this parameter, then it is very possible your temporary tablespace will grow again.

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.