This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Dec 7, 2012 9:54 AM by jgarry Go to original post RSS
  • 15. Re: Temp tablespace full
    Anand... Guru
    Currently Being Moderated

    I would suggest the below steps:-

    1. create a new temporary tablespace (TEMP2) with say 1GB size

    2. Make it as the default for the database.

    3. Drop the temp tablespace that was being used (big size) -- drop tablespace temp including contents; --> supposing temp as the name of the temporary tablespace

    NOTE:- Don't use " including contents and datafiles. "

    4. Recreate the temporary tablespace (temp) --> create temporary tablespace temp tempfile 'use/the/same/files/1' size xxxxM, 'use/the/same/files/2' size xxxxM;

    5. Drop the temp2 which you had made default. --> drop temporary tablespace temp2 including contents and datafiles;

    refer to smon not clearing my temp tablespace

  • 16. Re: Temp tablespace full
    635471 Expert
    Currently Being Moderated
    CrazyAnie wrote:
    Below is the query I am using to check the free and available space on a tablespace:

    SELECT /* + RULE */ df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024) "Size (MB)",
    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs,
    (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,df.bytes
    SELECT /* + RULE */ df.tablespace_name tspace,
    fs.bytes / (1024 * 1024),
    SUM(df.bytes_free) / (1024 * 1024),
    Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
    Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
    FROM dba_temp_files fs,
    (SELECT tablespace_name,bytes_free,bytes_used
    FROM v$temp_space_header
    GROUP BY tablespace_name,bytes_free,bytes_used) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

    This query retrieves me the used space on the TEMP segment to be 100%.
    "free" and "used" in the context of v$temp_space_header do not mean "currently free for new operations" and "currently used by an operation", they just indicate whether the space has been allocated to a temporary segment or not. When a segment is created in the temporary tablespace it can be in use or not in use.

    Check V$TEMPSEG_USAGE to see the true current temporary segment usage:
  • 17. Re: Temp tablespace full
    CrazyAnie Newbie
    Currently Being Moderated
    Okay, so that means my query for determining the used and free space on all the tablespaces in a DB needs to be modified so as to reflect the actual values.
  • 18. Re: Temp tablespace full
    Anand... Guru
    Currently Being Moderated

    select tablespace_name,sum(bytes_used/1024/1024)  USEB_IN_MB,sum(bytes_free/1024/1024) FREE_IN_MB  from v$temp_space_header group by tablespace_name;
  • 19. Re: Temp tablespace full
    ivw Journeyer
    Currently Being Moderated
    please check this
    to get know
    ->how many bytes used
    ->how many in total
    note 8192 is my db_block_size
    SQL> select tablespace_name,sum(bytes) total_bytes from dba_temp_files group by tablespace_name;
    ------------------------------ -----------
    TEMP                      28311552
    SQL> select tablespace,sum(blocks)*8192 used_bytes  from
    v$sort_usage group by tablespace;
    no rows selected
  • 20. Re: Temp tablespace full
    ivw Journeyer
    Currently Being Moderated
    Also you can join ( v$sort_usage.SESSION_ADDR = v$session.SADDR) to know which session is consuming more temp.
  • 21. Re: Temp tablespace full
    jgarry Guru
    Currently Being Moderated
    CrazyAnie wrote:
    Yes, the V$TEMPSEG_USAGE does not return me any rows. Also, when I try to execute the query on SQLPLUS, I am able to retrieve the results. But when I try running the same query through Business Objects using the Custom SQL option, i get the ORA-01652 error.
    Sounds like you should set a trace like alter system set events '1652 trace name errorstack level 12'; so you can look at the trace file and see what sql actually is getting executed. Just to be sure it is the same SQL as the one you tried.
  • 22. Re: Temp tablespace full
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl see if MOS Doc 1025288.6 (How to Diagnose and Resolve UNABLE TO EXTEND Errors) can help

  • 23. Re: Temp tablespace full
    CrazyAnie Newbie
    Currently Being Moderated
    Yeah, all the space in the TEMP tablespace finally got frred up without requiring to bounce the database. We are able ti execute all the queries perfectly now. Thanks to all for all your help!! :)
  • 24. Re: Temp tablespace full
    vk82 Explorer
    Currently Being Moderated
    Hi Crazy,

    Can u please let me know what steps u follow to sort out the problem.
  • 25. Re: Temp tablespace full
    978666 Newbie
    Currently Being Moderated
    SELECT tablespace_name,
    total_blocks*16/1024 as total_MB,
    used_blocks*16/1024 as used_MB,
    free_blocks*16/1024 as free_MB
    FROM v$sort_segment;
  • 26. Re: Temp tablespace full
    jgarry Guru
    Currently Being Moderated
    Please check the dates of the thread you are replying to before replying. It is usually better not to revive old threads.
1 2 Previous Next


  • Correct Answers - 10 points
  • Helpful Answers - 5 points