All,
We wanted to create a separate TEMP tablespace for an oracle user account which was used by some of the business users to write queries in Reporting Services. Some of the badly written queries are consuming huge amount of TEMP space and causing issues in our system.
We would like to know how can we create a separate TEMP tablespace and allocate that to that user, so that if there is any issue, that will not be affecting the other jobs in the system.
We tried to create a new TEMP tablespace and tried to allocate that as the defaukt TEMP tablespace for the using using ALTER USER statement. From the dba_users table, we can see that default TEMP tablespace for the user is the new one we created, but when we connect as that user and execute queries, it is still using the old tablespace.
What we are trying to do is feasible ? If yes, what are we missing.
DB version : 11.2.0.2
This is an Oracle Apps instance. (R12.1.3)
Let me know if we need any other information.
Cheers
AJ