Oracle Temporary table space usage

Today the user to execute a very simple SQL, the output result set is only tens of thousands of, involving three tables, the maximum is only 1 million of the data, the results are reported in the case of lack of table space, theory, such SQL should not eat so many temporary table space.

To query the usage of temporary tablespace:

Select Username,session_num,sql_id,tablespace,segtype,blocks*8192/1024/1024| | ' MB ' as MB from V$sort_usage order BY 6 desc

V$sort_usage displays information for each sort segment of a given instance, only actions that occur in the temporary tablespace will update the view. This is not just a sort action, as long as the temporary table space operation is logged, and the sort occurs in memory, and the view is not updated.

General fixed thinking I would think this SQL has a problem, and then I executed once, immediately thrown out of the abnormal, I immediately reacted to the wrong, because the SQL to take up the temporary table space is a lot of, is gradually 1.1 point request space occupied, this exception thrown too fast, and I went to inquire dba_temp _files, it is because of the design problem, the temporary table space created unreasonable, really is the space is too small for the sake of:

ALTER DATABASE Tempfile XXX autoextend on next 200m MaxSize unlimited;

Several scenarios occupied by temporal tablespace:

1. Order by or group by;

2, the creation and re-creation of the index;

3, DISTINCT, Sort-merge JOINS, HASH join operation;

4, Union & intersect & minus Sort-merge joins;

5. Other abnormal operation

Oracle Temporary table space usage