DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: temporary tablespace --sorting

  1. #1
    Join Date
    Jan 2001
    Posts
    60
    Hi

    Were do we specify temporary tablespace for sorting operations of long-runniing query?
    in general if we have small query it doesn'nt give a problem ,if we have big query it gives error why is it so ,how we can handle it ....

    Thanks
    lnr
    html code is off

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    alter user xyz temporary tablespace new_temp_tablespace

    will change the users temporary tablespace. If you are running out of TEMP space, you probably need to increase the size.
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    60
    i want to know were i specify the temporary table space for sorting operations ??????????
    html code is off

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    AFAIK, you can't specify a different tablespace than your default temporary tablespace for sorting.
    Jeff Hunter

  5. #5
    Join Date
    Jan 2001
    Posts
    60
    WHAT I MEAN IS ,IF AT ALL I KNOW THE FACT THAT ITS GOING TO BE LONG QUERY ,IN THAT CASE WERE I SPECIFY THE TEMPORARY TABLESAPCE FOR SORTING

    html code is off

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I guess I'm just not understanding the question. Could you elaborate?
    Jeff Hunter

  7. #7
    Join Date
    Jan 2001
    Posts
    60
    hI

    WHAT I WANT TO KNOW EXACTLY IS

    HERE IS MY QUESTION ,SINCE SOME ONE ASKED ME IN AN INTERVIEW WERE DO U SPECIFY THE TEMPORARY TABLESPACE FOR SORTING OPERATIONS OF A LONG-RUNNING QUERY .


    I KNOW THAT TEMP TABLESPACE IS CREATED FOR SORTING OPERATIONS ,IF I AM EXECUTING A BIG QUERY WERE MY TABEL SPACE IS NOT SUFFICIENT AND IT AFFECTS OTHER USERS TOO .iF U KNOW PRIORLY THE QUERY IS GOING TO BE LONG WERE WAHICH PARAMETER WE CHANGE IT

    THANKS FOR THE INFN

    THANKS
    LNREDDY
    html code is off

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    Um his very first reply tells you how to set the users temporary tablespace. The tablespace you specify there is used for sorting.

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    If you are thinking like using specific rollback segment for you big trasaction. Its not going to work like that. You can't mention your tablespace deaulted to what you want at the time of running query.

    You have to default it particular tablespace and run the query and it will be effective even after running the query until you change it back using "ALTER USER" command.

    Hope this helps.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    All sorting is done in the users default TEMPORARY tablespace. This is changed with an ALTER USER statement. I don't believe you can specify a TEMP tablespace for a query that is not your default temporary tablespace.
    Jeff Hunter

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