temporary tablespace --sorting

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

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.

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

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

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

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

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

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

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.

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.

I dont know i am correct or not

we can specify it in init.ora (SORT_AREA_SIZE) ,we can increase it by looking into the perfomance by

select name, value
from v$sysstat
where name like `sort%

Obviously, the lower the value of the sorts to disk, the better the sort is performing. However, having many sorts to disk does not necessarily mean that the database is not sorting optimally

based on this criteria i can change it i think

If i am wrong please correct me .

Thanks
lnreddy

You are confusing seems with sorting in Memory and in temp tablespace. Two different concepts.

http://technet.oracle.com/docs/produ...paces.htm#9566

http://technet.oracle.com/docs/produ...9_mem.htm#2130