temporary tablespace --sorting

[lnr]

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

[marist89]

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.

[lnr]

i want to know were i specify the temporary table space for sorting operations ??????????

[marist89]

AFAIK, you can't specify a different tablespace than your default temporary tablespace for sorting.

[lnr]

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

[marist89]

I guess I'm just not understanding the question. Could you elaborate?

[lnr]

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

[pwoneill]

Um his very first reply tells you how to set the users temporary tablespace. The tablespace you specify there is used for sorting.

[sreddy]

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.

[marist89]

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.