confused over temporary tablespace for user

[davo]

hi,
we have a temporary tablespace which is set as the temporary tablespace against a user.
However, the tablepsace is always empty (only header info in it)
Presumably the actual tablespace being used by that user is a different tablespace to the default?

How can I tell which tablespace is actually being used for that user, as opposed to the default ts specified.
Also, how is it possible to use another one anyway? - can it be specified somewhere in the sql statements etc- I am not aware of this being possible?

Am I right in thinking that if the ts is basically empty that it has never been used, or does it fill up and empty again depenant upon wha the user is doing, and so if it is empty then tht does not necessarily mean it is not being used at some point?

[davey23uk]

maybe the user isnt using any temp therefore it remains empty

[davo]

sorry, should have also pointed out that this is on 8i db and the tablespace concerned is actually defined as a permanent ts - it is not a proper temporary tablespace as in 9i.
I have looked in dba_segments and nothing exists for this tablespace.
Obviously nothing would be written in a 9i temporary tablespace environment, but then I would see something in dba_temp_files instead.
What happened in 8i though, that is what I need to know.

[davo]

how can I see when it is being used?
Can I run a select distinct on a big table and then look somewhere as sys whilst that is running (equivelant place to dba_temp_files in 9i whatever it was in 8i)?

[tamilselvan]

First, check in DBA_USERS view what tablespace (for temporary purpose) is defined for a user.

select username, TEMPORARY_TABLESPACE from dba_users;

Then you can test with "create index script" or "select c1, sum(c2) from 10M_rows_big_table group by c1 "

Tamil