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 email@example.com
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.
the diff between the select statment s 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.