Determine Queries Temporary Space Requirement

If you are ever in the situation where someone doesn't want to add disk space to the temporary tablespace without having the specifics as to exactly how much space is required. There are two events that can be set that will provide this information.

The key is that you will actually need to run the query in question in order to get all the sort information required.

alter session set events '10032 trace name context forever';
alter session set events '10033 trace name context forever';

10032 will provide all the sort information to include the total amount of records input and output as well as the total amount of space required.:


10033 will provide the details of the sort run to include the block address and the number of blocks. At the end it will total the blocks used for all the sorts. This total number will be the amount of disk space required for this query with this data set.

Both of these traces express the disk space required in blocks, so make sure you multiple that number by the block size of the database.