This content has been marked as final. Show 6 replies
alter user changes the values permanently.
For changes at session level you would need "alter session" but as far as I can tell it does not support a change of temporary tablespace.
Edited by: Robert Geier on Jan 29, 2010 9:09 AM
Temporary tablespace is set at database level and user level. It is not set at the session level.
The below is an extract from Oracle Docs for 10gR2
You can specifically assign a temporary tablespace for the user but not for the session.
The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement creates a default temporary tablespace for the database. Oracle Database assigns this tablespace as the temporary tablespace for users who are not explicitly assigned a temporary tablespace. You can explicitly assign a temporary tablespace or tablespace group to a user in the CREATE USER statement. However, if you do not do so, and if no default temporary tablespace has been specified for the database, then by default these users are assigned the SYSTEM tablespace as their temporary tablespace.
For a specific batch of jobs I have setup
with the proviso that no other job/program/user connects as the same database account when the batch is running at step c.
a. Create dedicated NEW_TEMP tablespace on additional storage b. ALTER USER username TEMPORARY TABLESPACE NEW_TEMP c. Run the batch d. ALTER USER username TEMPORARY TABLESPACE NORMAL_TEMP e. DROP TABLESPACE NEW_TEMP
Hemant K Chitale