Oracle ORA-01652 troubleshooting

Symptom: ORA-01652: unable to extend TEMP segments through 128 (in tablespace temp)

Generally, after the Select statement and create index are sorted using the TEMP tablespace, Oracle Automatically releases the temporary segment. However, if some temporary segments are not released, the TEMP tablespace is almost full, and even the database is restarted, the problem remains unsolved. Below I will summarize several solutions.

Method 1. Restart the database

When the database is restarted, the Smon process will release the temporary segment and clean up the TEMP tablespace. However, in many cases, our database cannot be down, therefore, this method lacks some application opportunities, but it is still very useful.

Method 2: Metalink provides a method

Modify the storage parameters of the TEMP tablespace so that the Smon process can view the temporary segments to clean up and TEMP tablespace.

SQL> alter tablespace temp increase 1;

SQL> alter tablespace temp increase 0;

Method 3: a common method is described as follows:

1. Run the following statement a to check who is using the temporary segment.

SELECT username,

Sid,

Serial #,

SQL _address,

Machine,

Program,

Tablespace,

Segtype,

Contents

FROM v $ session se,

V $ sort_usage su

WHERE se. saddr = su. session_addr

2. processes that are using temporary segments

SQL> Alter system kill session 'sid, serial #';

3. shrink the TEMP tablespace.

SQL> Alter tablespace TEMP coalesce;

Method 4: One way to diagnose an event is also considered a "killer" method.

1. Determine the ts of the TEMP tablespace #

SQL> select ts #, name from sys. ts $;

TS # NAME

-----------------------

0 SYSYEM

1 RBS

2 USERS

3 * TEMP

4 TOOLS

5 INDX

6 DRSYS

2. Perform the cleanup operation.

SQL> alter session set events 'immediate trace

Name DROP_SEGMENTS level 4 ';

Note:

Temp tablespace TS # is 3 *, So TS # + 1 = 4

Others:

1. I think the reason for the above problem may be that the large sorting exceeds the allowed range of the TEMP tablespace space. It may also contain other causes of exceptions.

2. Observing the states of TEMP and other spaces is one of Dba's daily responsibilities. We can use Toad, Object Browser, and other tools, or use the following statement:

Select upper (F. TABLESPACE_NAME) "tablespace name ",

D. TOT_GROOTTE_MB "tablespace size (M )",

D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M )",

TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 100,

2 ),

'1970. 99') "usage ratio ",

F. TOTAL_BYTES "Idle space (M )",

F. MAX_BYTES "maximum block (M )"

FROM (SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES,

ROUND (MAX (BYTES)/(1024*1024), 2) MAX_BYTES

From sys. DBA_FREE_SPACE

Group by TABLESPACE_NAME) F,

(Select dd. TABLESPACE_NAME,

ROUND (SUM (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB

From sys. DBA_DATA_FILES DD

Group by dd. TABLESPACE_NAME) D

Where d. TABLESPACE_NAME = F. TABLESPACE_NAME

Order by 4 DESC;

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store