For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.
However, turns out this is ineffective IF you use:
ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;
This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query on a prod DB blowing out the main TEMP space.
Deduction quickly suggested that the above must be the case and it was quickly proven by a test case and also supported by the documentation .
the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects
Obviously… not using ALTER SESSION and using fully qualified object names prevents this “vulnerability”… but that’s not good enough unfortunately.