title: ORA-01652 - unable to extend temp segment by 4096 in tablespace (oracle 10)
body: <p>Problem:</p>
  
  <p>I'm new to Oracle, and I think I'm missing some basic knowledge that is causing my temp tablespace to fill up.</p>
  
  <p>I'm opening a connection to my database and running a pl/sql procedure multiple times to insert rows. Each time I run the procedure, the number of free blocks decreases in my TEMP tablespace. When the number of free blocks gets too low, the procedure will fail with the error "ORA-01652 - unable to extend temp segment by 4096 in tablespace". If I close the database connection, the free blocks in the TEMP tablespace resets to the total number of blocks, and I can continue rerunning the procedure. How do I free up the TEMP tablespace blocks without having to close and open the database? I thought I needed to add a commit statement, but that didn't help.</p>
  
  <p>Thanks</p>
  
  <hr>
  
  <p>Code:</p>
  
  <p>Query to check free_MB (this decreases each time I run the procedure).</p>
  
  <pre><code>SELECT tablespace_name,
  total_blocks,
  used_blocks,
  free_blocks,
  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; 
  </code></pre>
  
  <p>SQL I run multiple times until free_mb reduces to 0 and I get errors:</p>
  
  <pre><code>DECLARE
    p_samples LOG_ENTRY_ARRAY;
    longSample clob;
  BEGIN
    For v_COUNTER IN 1..32767 LOOP
      longSample := longSample || 'a';
    END loop;
    -- initialize the input
    p_samples := LOG_ENTRY_ARRAY(longSample, 'short sample');
    for i in 1..100 LOOP
      INSERT_SUMMARY_SAMPLES('TABLE1', 1000, 1, 2, p_samples);
    END loop;
    commit;
  END;
  </code></pre>
  
  <p>The procedure being called which does a bunch of inserts into two tables:</p>
  
  <pre><code>create or replace 
  PROCEDURE INSERT_SUMMARY_SAMPLES 
  (
    p_TABLE_NAME IN VARCHAR2  
  , p_TS IN NUMBER  
  , p_SIGNATURE_ID IN NUMBER  
  , p_COUNT IN NUMBER  
  , p_SAMPLES IN LOG_ENTRY_ARRAY  
  ) AS 
    tbl_summary varchar2(30);
    tbl_samples varchar2(30);
    summary_id number(10,0);
    sample varchar2(32767);
  BEGIN      
    tbl_summary := 'TBL_' || p_TABLE_NAME || '_SUMMARIES';
    tbl_samples := 'TBL_' || p_TABLE_NAME || '_SAMPLES';
  
    -- insert summary and get the id
    EXECUTE IMMEDIATE 'INSERT INTO ' || tbl_summary 
      || ' (agg_start_ts, signature_id, count, num_samples) VALUES (:a,:b,:c,:d) returning id into :1' 
      using p_ts, p_signature_id, p_count, p_SAMPLES.count returning into summary_id;
    dbms_output.put_line('new summary_id is : ' || summary_id);
    -- insert samples
    FOR i in 1..p_SAMPLES.count LOOP
      -- convert clob to varchar2
      CLOB_TO_VARCHAR(p_SAMPLES(i),sample);
      EXECUTE IMMEDIATE 'INSERT INTO ' || tbl_samples || ' (summary_id, log_entry) VALUES (:a,:b)' using summary_id, sample;
      -- dbms_output.put_line('insert sample : ' || TO_CHAR(p_SAMPLES(i)));
    END LOOP;  
  END INSERT_SUMMARY_SAMPLES;
  </code></pre>
  
  <p>CLOB_TO_VARCHAR is another procedure:</p>
  
  <pre><code>create or replace 
  PROCEDURE CLOB_TO_VARCHAR (
    p_clob IN CLOB,
    p_varchar OUT VARCHAR2
    )
  AS
    v_output varchar2(32767);
    l_amount BINARY_INTEGER := 32767;
    l_pos INTEGER := 1;
    l_clob_len INTEGER := 0;
  BEGIN
    l_clob_len := DBMS_LOB.getlength (p_clob);
    WHILE l_pos &lt; l_clob_len
    LOOP
      dbms_lob.READ(p_clob, l_amount, l_pos, v_output);
      l_pos := l_pos + l_amount;
    END LOOP;
    p_varchar := v_output;
  END CLOB_TO_VARCHAR;
  </code></pre>