oracle临时表空间地址,Oracle 临时表空间管理

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.8 to

11.2.0.1.0 [Release 9.2 to 11.2]

PURPOSE

This document can be used to approach the

'ORA-1652: unable to extend temp segment by %s in tablespace %s'

error in non-RAC environments.

For RAC environments,

useNote

280578.1- Troubleshooting

ORA-1652 Errors in RAC.

TROUBLESHOOTING STEPS

Error explanation

This error is fairly self explanatory - we

cannot get enough space for a temporary segment. The size reported

in the error message is the number of contiguous free Oracle blocks

that cannot be found in the listed tablespace.

How to approach the ORA-1652

error

There are two views in the database that help

keep track use in the temporary tablespace (and can be queried

during transactions to view usage): v$sort_usage and

v$tempseg_usage (from 9i onwards).

In order for the approach to be relevant, the

following investigation steps should be followed when the error

occurs (i.e. as soon as the problem is spotted and before any sql

performing a sort finishes) :

Check the status of the sort segment

utilization :

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

If USED_BLOCKS = TOTAL_BLOCKS, find which user

and statement is using the temporary sort segment

by following:

Note 317441.1- How Do You Find

Who And What SQL Is Using Temp Segments

In some cases, you may find

that the ORA-1652 is not reported for

a temporary tablespace, but a permanent one. This

is not an abnormal behaviour and it can occur for

example while creating or dropping objects like tables and indexes

in permanent tablespaces. Reference

:Note

19047.1 - OERR: ORA 1652 unable to extend temp

segment by %s in tablespace %s In such cases the following note will be of

use :

Note

100492.1- ORA-01652: Estimate

Space Needed to CREATE INDEX

If the tablespace in which the TEMPORARY segment resides is of

type PERMANENT, also check that the following events are not set in

the initialization parameter file:

event="10061 trace name context forever, level 10"

event="10269 trace name context forever, level 10"

If they are set, unset them and restart

database.

These two events prevent SMON from cleaning

up.

Reference :Note

1039341.6- Temporary

Segments Are Not Being De-Allocated After a Sort

In the process of diagnostic and tuning, the

resumable statement feature can be useful. It allows the DBA, once

having applied the appropriate solution to the space allocation

issue, to resume the suspended transaction which does not lose all

the work done previously. By querying dba_resumable one can find

the statement that is executed when ORA-1652 occurs.

SeeNote

136941.1- Using RESUMABLE

Session to Avoid Transaction Abort Due to Space Errors.

There are two ways of solving this error:

Add more tempfiles, increase the size of the

current ones or enable auto extend and the most important:

Tune the queries/statements so that the sort

operations are done in memory and not on the disk.

Note that the extents allocated for a user's

sort segment are NOT deallocated but are marked as FREE from

performance reasons. The FREE extents can be further used by other

users that are executing sort

operations. After the database

restart the allocated extents are NOT released either but are FREE

to be reused.

Hence, seeing the physical space

of the temporary tablespace fully allocated is not a reason to be

concerned per se. The above query of V$SORT_SEGMENT should be used

to establish the free space inside the temporary

tablespace.

Known issues

Note 463819.1- Database HANG

After Migrating to 10.2 :

ORA-1652

Note 164850.1- ORA-01652 in

Resumable Statements Prevents any SELECT on DBA_RESUMABLE

View

Note 750209.1- Temp LOB space

not released after commit: ora-1652 being hit

REFERENCES

NOTE:317441.1-

How Do You Find Who And What SQL Is Using Temp Segments

NOTE:1267351.1- TROUBLESHOOTING

GUIDE (TSG) : ORA-1652: unable to extend temp segment

NOTE:1039341.6- Temporary

Segments Are Not Being De-Al