Temp space keeps increasing

[CaoCao]

I notice that the temporary tablespace has grown to almost 7GB. What constitutes to the increase? How can I reduce the size of it?

[abhaysk]

Set max size.

[engiri]

Restart the server in restrict mode.
Resize the temp ts to minimum required size.
Give maximum size.

[ljayanth]

Restarting the DB in restrict mode is good idea

[k.chithra]

Hello,

Restarting the database will release the space in Temp tablespace
But in restricted mode ??

If users cannot access the database ,what is the use of having a database ?

Once users start using it ,might be again temp tablespace might increase depending on the type and number of transactions.

So,
Querying v$sort_segment to find out used and free blocks periodically.

Query v$sort_usage,v$sqltext to find which user and sql statements are using it ,might help to solve the issues I guess.

[engiri]

restart in restrict mode.
after resizing
allow all.

[anandkl]

Hi All,
Its always better to know what is causing the TEMP tablespace size is increasing, rather than decreasing or increase the size of the tablespace.It might happen that tomorrow you might need more TEMP space for SORTING activity and your SQL's might error out.

So when ever any SORTING SQL's are being executed its always better to set the sort_area_size for that session.This would help in sorting being done in MEMORY and reducing I/O and improves the performance of the SQL's

regards
anandkl

[abhaysk]

Originally posted by anandkl
Its always better to know what is causing the TEMP tablespace size is increasing, rather than decreasing or increase the size of the tablespace.

But first you gotta limit it, wont u?

[CaoCao]

v$sort_segment's blocks are fully used.

But what if I restrict the size to e.g. 2GB without extent but in the future more temp space is required in the SQL, will it cause any errors?

[anandkl]

If the SQL requires more TEMP space and if it does not find it, obviously it would error out.

regards
anandkl