Temp Tablespaces and Segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Temp Tablespaces and Segments

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello DBA's:
    My query is failing due to Temp TS (extents getting over). Where can I check the total size allocated for the Tablespace. Checked v$sort_segment to find out that my Tablespace has 358016 total_extents. my db_block_size is 8k and so I presume that my Temp TS is 2.8GB. Checked other views (dba_data_files, dba_free_space, dba_extents) but could not find any info on Temp TS. I also want to find out what filesystem my Temp TS belongs to, before I add. The query eats up the Temp TS very fast (15 minutes) and fails.
    Please advise.
    Thanks ST2000

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    look dba_temp_files

    is this really *your* tablespace? seems like you dont even know how it was created

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    You are right Pando.. I have not created this database and I just started in this place.. If you look at original message, it shows as though Temp has 2.8GB, but truly (checking datafiles) I could see allocation of 4 datafiles equalling 3.8GB and I am wondering why 1 GB is not available..

    Please look at my findings..

    1 select substr(tablespace_name, 1,10) tablespace, substr(file_name, 1,30) file_name, file_id,
    2 bytes, status, autoextensible, user_bytes, user_blocks
    3* from dba_temp_files
    SQL> /


    TABLESPACE FILE_NAME FILE_ID BYTES STATUS AUT USER_BYTES USER_BLOCKS
    ---------- ------------------------------ ---------- ---------- --------- --- ---------- -----------
    TEMP /u03/dbs/ODBMSPRD/temp01.dbf 1 1048576000 AVAILABLE NO 1047527424 127872
    TEMP /u09/dbs/ODBMSPRD/temp02.dbf 2 AVAILABLE
    TEMP /u07/dbs/ODBMSPRD/temp03.dbf 3 1048576000 AVAILABLE NO 1047527424 127872
    TEMP /u12/dbs/ODBMSPRD/temp04.dbf 4 838860800 AVAILABLE NO 837812224 102272


    For the fileID 2, it shows as available but no bytes shown..

    From unix:

    oracle@CG_JDA1:/u09/dbs/ODBMSPRD_> ls -l t*.dbf
    -rw-r----- 1 oracle dba 1048584192 May 30 11:00 temp02.dbf

    what does this mean? Where is the 1GB allocated to fileID 2.

    Thanks, ST2000

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from v$tempfile;

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Thanks Pando..
    I see the file2 is offline..

    SQL> alter database datafile '/u09/dbs/ODBMSPRD/temp02.dbf' online;
    alter database datafile '/u09/dbs/ODBMSPRD/temp02.dbf' online
    *
    ERROR at line 1:
    ORA-01516: nonexistent log file, datafile or tempfile '/u09/dbs/ODBMSPRD/temp02.dbf'

    why would this happen ?

    Thanks, ST2000

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it's tempfile not datafile

  7. #7
    Join Date
    Oct 2000
    Posts
    449
    Sorry.. My command was for datafile, when it errored.. Now that I have it with tempfile instead of datafile it worked..

    Thanks, ST2000

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    by the way next time look file date as well, it indicates it has not been accesed since May

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