Unable to extend temp tablespace

[alison]

Hi there,

A query which I'm running for a user keeps failing with

"ORA-01652: unable to extend temp segment by 40964 in tablespace TEMPORARY_DATA :-1652"

error message.

The query has a like criteria in it which is causing the problem,
"AND USER_DATA_FIELD_14 Like 'Y%"

The user_data_field_14 column has 2 values in it 'YES' or 'NO', unfortunately the field also has many ' ' spaces in it.

If I use the command
"AND RTRIM(USER_DATA_FIELD_14,' ') = 'YES'
it runs correctly,

However the script is being created through an application and you can't manually change it to include the "RTRIM".

I can't increase the temporary_data tablespace (not enough disk)

Can anyone suggest anything else I can do to make the query run.

Many thanks

Alison

[dknight]

When you say there are spaces in the field, do you mean for example:

' YES' and 'YES' and ' YES' ?

Is it possible to clean the data? You could update the inconsistent fields and then perhaps apply a bitmap index to that field.

[rohitsn]

Hi,
Temporary tablespace can be used by many users to perform sorting and other creation of database objects such as indexes.
The solution to the problem is to shutdown the database and then try it out.When u shutdown the database Oracle flushes all the temporary segments.
If u have more memory than u can increase the SORT_AREA_SIZE in init.ora paramter file.Since when Oracle needs some sorting it will be done in memory first and then if it is insufficent it will be done in temporary tablespace.So i u have more memory increase the parameter.
In case of any help please be free to ask me at rohitsn@altavista.com

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

[FendleRG]

Use Data Designer in PeopleSoft to create a new, custom view containing the SQL that works without the temporary space issue. Then point Query to that view instead of the base object. I just assumed you are having problems with Query or nVision since SQR, COBOL, and AppEngine can be customized with your version of the SQL.

[shawish_sababa]

Hi

the diff between the select statment s this:
useing this:
"AND USER_DATA_FIELD_14 Like 'Y%"
has a complitly diff excution plan then the second option:
AND RTRIM(USER_DATA_FIELD_14,' ') = 'YES' .
in the last option you use index serch (if you have one) when you use like no index serch, more sorting to do (to make it simple). your problm is not size, but tunning of the where part in your select statment.

[alison]

Hi shawish_sababa,

Through Business Objects I have amended the expression in the where clause to read

AND RTRIM(USER_DATA_FIELD_14,' ') = 'YES'

There is already an index on that column, however the query still fails with the same error, though this time the error is

"ORA-01652: unable to extend temp segment by 8100 in tablespace TEMPORARY_DATA :-1652"

the numbers are much lower.

Any help would be greatly appreciated.

Regards as always

Alison