Temp tablespace been fill up and with error ORA-1652

CAUSE DETERMINATION
===================
--) When the temporary tablespace fills, any statement that requires additional temporary space will fail with an “ORA-1652:
unable to extend temp segment” error.

CAUSE JUSTIFICATION
===================
--) Temporary segments in temporary tablespaces—which we will call “sort segments”—are owned by the SYS user, not
the database user performing a sort operation. There typically is just one sort
segment per temporary tablespace, because multiple sessions can share space in
one sort segment. Users do not need to have quota on the temporary tablespace
in order to perform. sorts on disk. In fact, quotas on temporary tablespaces are
ignored by Oracle.

One SQL statement can cause multiple sort operations, and one database session can have multiple SQL statements active at the same
time—each potentially with multiple sorts to disk. When the results of a sort
to disk are no longer needed, its blocks in the sort segment are marked as no
longer in use and can be allocated to another sort operation.

A sort operation will fail if a sort to disk needs more disk space and there are 1.) no unused
blocks in the sort segment, and 2.) no space available in the temporary
tablespace for the sort segment to allocate an additional extent. This will
most likely cause the statement that prompted the sort to fail with the Oracle
error, “ORA-1652: unable to extend temp segment.” This error message also gets
logged in the alert log for the instance.


Instead of waiting for a temporary tablespace to fill and for statements to fail, you can
monitor temporary space usage in the database in real time. At any given time,
Oracle can tell you about all of the database’s temporary tablespaces, sort
space usage on a session basis, and sort space usage on a statement basis , as
below :

>) Temporary Segments
==================

The following query displays information about all sort segments in the database.

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;

The above query displays for each sort segment in the database the tablespace the segment resides in, the
size of the tablespace, the amount of space within the sort segment that is
currently in use, and the amount of space available.

>) Sort Space Usage by Session
======================

The following query displays information about each database session that is using space in a sort segment. Although one
session may have many sort operations active at once, this query summarizes the
information by session

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;

The query displays information about each database session that is using space in a sort segment, along with the amount
of sort space and the temporary tablespace being used, and the number of sort
operations in that session that are using sort space.

>) Sort Space Usage by Statement
========================

The following query displays information about each statement that is using space in a sort segment.

SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

The query displays information about each statement using space in a sort segment, including
information about the database session that issued the statement and the
temporary tablespace and amount of sort space being used.


We suggest you to use the above sql's at regular intervals , which can help you to indentify the
sql which takes away the Temp space.

If you come across any particular sql has the high temp space usage , all the times, then we suggest it is the right
candidate for query tuning.

By doing this we can restrict in getting the above error message ie : ORA-1652.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92530/viewspace-563078/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92530/viewspace-563078/