Re: Need help on "alter tablespace" command.

kalyanas_at_stimpy.eecis.udel.edu (Pramod Kalyanasundaram) writes:

Oracle must be building a temporary segment in temptabs.

Three things to try:

1. Increase your sort area size.
2. Throw disk at the problem. Increase temptabs.
3. Rewrite it to use a where exist instead of a where in. You need an index on FIELDA to make this work. Tom

>Hi!
> Could someone help me with the "alter tablespace" command on ORACLE
> 7.0. The scenario is as follows:
 

> There is a table (TABLEA) that has around 25000 records and another
> table (TABLEB) that has around 4000 records. FIELDA is common to
> both TABLEA and TABLEB.
 

> When the following command is issued,
 

> DELETE FROM TABLEA
> WHERE LENGTH(FIELDA) = 11 AND
> SUBSTR( FIELDA, 1, 8 ) IN
> (SELECT (FIELDA) FROM TABLEB)
 
> ORACLE responds with the following error message.
 

> Unable to extend temp segment by 18 in tablespace TEMP.
 

> The manual suggests running the "alter tablespace" command when this
> error is encountered. When this command was used to change the initial
> extent parameter, the number (18) kept decreasing until a particular
> point (12) and then started increasing (147) again. Could someone throw
> some light on what is happening.
 

> Any help is appreciated.
> Cheers
> Pramod.
Received on Sun Feb 05 1995 - 08:53:00 CET