How can we use oracle temp space effectively?

We are facing some issue with oracle temp space.while inserting the data into table its quickly filling up and failing with unable to extend temp segment error. is there any way to overcome this scenario without adding extra temp file? Any suggestions? your responses would be really appreciated.

enter image description here

1 answer

  • answered 2018-07-12 11:41 Bob Jarvis

    To get the total allocated space, used space, and available free space in your tablespaces you can use the following query:

    -- Available space, by tablespace
    
    SELECT * FROM
      (SELECT tablespace_name FROM dba_tablespaces)
    LEFT OUTER JOIN
      (SELECT tablespace_name, SUM(bytes) AS total_bytes
         FROM dba_data_files
         GROUP BY tablespace_name)
      USING (tablespace_name)
    LEFT OUTER JOIN
      (SELECT tablespace_name, sum(bytes) AS used_bytes
         from dba_segments
         GROUP BY tablespace_name)
      USING (tablespace_name)
    LEFT OUTER JOIN
      (SELECT tablespace_name, SUM(bytes) AS free_bytes
         FROM dba_free_space
         GROUP BY tablespace_name)
      USING (tablespace_name);
    

    There really isn't a way to "control" your use of temp space. The database will do what it needs to do. But inserting 3.5 million rows at one go is a lot of data. I suggest you restructure your population procedure to commit intermittently.

    Best of luck.