CODEWITHSUNDEEP
oracle-databasestored-procedures
walter2011
walter2011

Reputation: 125

ORA-01652 - unable to extend temp segment by 4096 in tablespace (oracle 10)

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;

Upvotes: 0

Views: 7468

Answers (2)

B&#233;la
B&#233;la

Reputation: 284

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

Upvotes: 0

Balaji Katika
Balaji Katika

Reputation: 3005

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?

Upvotes: 0

Related Questions