0

Problem:

I'm new to Oracle, and I think I'm missing some basic knowledge that is causing my temp tablespace to fill up.

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.

Thanks

Code:

Query to check free_MB (this decreases each time I run the procedure).

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;

SQL I run multiple times until free_mb reduces to 0 and I get errors:

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;

The procedure being called which does a bunch of inserts into two tables:

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;

CLOB_TO_VARCHAR is another procedure:

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 < 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;
Raj
  • 10,653
  • 2
  • 45
  • 52
walter2011
  • 125
  • 1
  • 8
  • Did you ever figure this out? It sounds like I'm hitting a very similar situation and I'm wondering what you discovered – Johnny Nov 02 '17 at 13:46

2 Answers2

0

Your TEMP table space is getting filled fast. You might need to increase the tablespace manually. Possible duplicate of ORA-01652 Unable to extend temp segment by in tablespace and

ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?

Community
  • 1
  • 1
Balaji Katika
  • 2,835
  • 2
  • 21
  • 19
  • Why isn't the temp space freed after my call to the procedure finishes? It looks like the temp space only frees when I close the connection to the DB. My application calling the procedure uses a c3p0 connection pool and I think that never actually closes connections. How do you use a long running connection pool if doing so causes memory to be used up? – walter2011 May 15 '15 at 04:39
  • I think its logically correct to free up temp space only after closing the connection. Reg using long running connection pool, do you mean how to avoid memory outages of temp table space. If so, my suggestion would be use short transactions wherever possible (frequent commits) as they might free up temp space. I would prefer increasing tablespace by adding more space (datafiles) using ALTER TABLESPACE temp ADD TEMPFILE – Balaji Katika May 15 '15 at 05:31
  • In my testing, running commit doesn't free the temp space. Only closing the connection does. I did more analysis and it looks like the temp space is used by LOBs. I guess I need to find out why the CLOB is using space until the connection session ends. – walter2011 May 15 '15 at 07:27
0

I would guess that you've got a temporary lob somewhere but it's not being explicitly freed. Where is LOG_ENTRY_ARRAY defined?

Béla
  • 284
  • 1
  • 2
  • 8