Unable to extend temp tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Unable to extend temp tablespace

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.

    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    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

  4. #4
    Join Date
    May 2001
    Location
    Dallas Texas
    Posts
    8
    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.

  5. #5
    Join Date
    Jul 2000
    Posts
    243
    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.




  6. #6
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width