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 ....
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
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.
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.