Oracle TEMPORARY TABLESPACE

Question

I want to import a database to a new tablespace.

So I;m setting up a user using CREATE USER. But how do I set up the TEMPORARY TABLESPACE if I don't know the temporary tablespace the existing database uses?

Answer 1

SELECT  temporary_tablespace
FROM    dba_users
WHERE   USERNAME = 'SCOTT'

Answer 2

If you are creating a user and don't specify a temporary tablespace, it should get set to the default. This can be found by:

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

If you want to find other TEMPORARY tablespaces within the database you can do:

SELECT * FROM dba_tablespaces WHERE contents = 'TEMPORARY' ORDER BY tablespace_name;