confused over temporary tablespace for user
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: confused over temporary tablespace for user

  1. #1
    Join Date
    Aug 2005
    Posts
    69

    confused over temporary tablespace for user

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    maybe the user isnt using any temp therefore it remains empty

  3. #3
    Join Date
    Aug 2005
    Posts
    69
    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.

  4. #4
    Join Date
    Aug 2005
    Posts
    69
    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)?

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width