A temporary tablespace is a special set of datafiles associated with an Oracle database that contain data that is not required for read-consistency or for any recovery. The most obvious is for sorting of datasets that are too large to fit into memory (the PGA, or Program Global Area), but it is also used for other operations that require non-permanent storage. It is also used for storing information in global temporary tables.
If any session or operation uses up a threshold amount of memory, then Oracle breaks the operation into smaller ones that can each be performed in memory. Partial results are written to disk in a temporary tablespace. The threshold for how much memory may be used by any one session is controlled by setting instance parameters. If the WORKAREA_SIZE_POLICY parameter is set to AUTO, then the PGA_AGGREGATE_TARGET parameter indicates how much memory can be used collectively by all sessions for activities such as sorting and hashing. Oracle will automatically assess and decide how much of this memory any individual session should be allowed to use. If the WORKAREA_SIZE_POLICY parameter is set to MANUAL, then instance parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, and BITMAP_MERGE_AREA_SIZE dictate how much memory each session can use for these operations.
Generally while creation of any database schema we mentioned temporary tablespace clause.
For instance, create use **** identified by **** temporary tablespace "temp" account unlock;
So, ORA-1652 occur when any user session execute the sql and performing sorting operation on huge amount of data.
Following simple steps we can identified that session and sqls.
From 12c onward :
SELECT * FROM DBA_TEMP_FREE_SPACE;
For 11g :
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
From below queries we can identified top session which utilized more temp tablespace means huge sort operation:
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
If it is RAC environment then please use GV$ or execute on all the nodes above sql.
Check all the session we got from above sql and take necessary action, like killing non application user session, or ask development team to fine tune the queries which use huge sorting or hashing on large number of data.
Conclusion
When an operation such as a sort or hash is too large to fit in memory, Oracle allocates space in a temporary tablespace for intermediate data to be written to disk. Temporary tablespaces are a shared resource across the database, and you can not set the quotas to limit temporary space used by one session or database schemas. If a sort operation runs out of space, the statement initiating the sort will fail. It may only take one query missing part of its WHERE clause to fill an entire temporary tablespace and cause many users to encounter failure because the temporary tablespace is full.It is easy to detect when failures have occurred in the database due to a lack of temporary space. With the setting of a simple diagnostic event, it is also easy to see the exact text of each statement that fails for this reason. There are also v$ views that DBAs can query at any time to monitor temporary tablespace usage in real time. These views make it possible to identify usage at the database, session, and even statement level ( as mentioned above ).
0 Comments