Unable to extend temp segment by 128 in tablespace TEMP. Another option to execute that query?

Question

I am new in Oracle SQL and I am trying to make an update of a table with the next context:

I have a table A:

+---------+---------+---------+----------+
| ColumnA | name    | ColumnC | Column H |
+---------+---------+---------+----------+
| 1       | Harry   |  null   | null     |
| 2       | Harry   |  null   | null     |
| 3       | Harry   |  null   | null     |
+---------+---------+---------+----------+

And a table B:

+---------+---------+---------+
| name    | ColumnE | ColumnF |
+---------+---------+---------+
| Harry   | a       |  d      |
| Ron     | b       |  e      |
| Hermione| c       |  f      |
+---------+---------+---------+

And I want to update the table A so that the result will be the next:

+---------+---------+---------+----------+
| ColumnA | name    | ColumnC | Column H |
+---------+---------+---------+----------+
| 1       | Harry   |  a      | d        |
| 2       | Harry   |  a      | d        |
| 3       | Harry   |  a      | d        |
+---------+---------+---------+----------+

I have an issue with an Oracle SQL sentence. I have the next context:

merge into tableA a
using tableB b
on (a.name=b.name)
when matched then update set
columnC = b.columnE,
columnH = b.columnF


create table tableA (columnC varchar2(20), columnH varchar2(20), name varchar2(20), columnA number);
create table tableB (columnE varchar2(20), columnF varchar2(20), name varchar2(20));
insert into tableA values (null, null,'Harry',1);
insert into tableA values (null, null,'Harry',3);
insert into tableA values (null, null,'Harry',3);
insert into tableB values ('a', 'd','Harry');
insert into tableB values ('b', 'e','Ron');
insert into tableB values ('c', 'f','Hermione');
select * from tableA;
merge into tableA a
using tableB b
on (a.name=b.name)
when matched then update set
columnC = b.columnE,
columnH = b.columnF;
select * from tableA;

The problem is that I get the next error when I execute that command:

Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

I cannot give more space to TEMP tablespace. So, my question is: Is there any option to use another SQL query that doesn't use TEMP tablespace?

Out of curiosity, why can't you increase the TEMP tablespace? Have you run out of space on the server or something and can't physically add any more to it? Or is it pushback from your DBAs or something? Because, IMO, if your system needs to be able to run a well-tuned query that consumes 12GB of temp tablespace, then it needs a temp tablespace that is at least 12GB. It's only for one-off, ad-hoc queries that I'd not bother requesting my DBAs to add more temp tablespace. — Boneist

schurik schurik · Accepted Answer · 2016-10-05T08:30:36

you can try the following query maybe it will consume less TEMP tablespace:

update tableA 
set (columnC, columnH ) = (select ColumnE, ColumnF from tableB where tableB.name = tableA.name)
where 
  tableA.name in (select tableB.name from tableB)
;

Or you can try to perform an update in small chunks in a loop. It's less perfomant, but if you have no other way ...

    begin 
      FOR rec in 
      (select name, ColumnE, ColumnF from tableB)
      LOOP
       update tableA 
       set  
         columnC = rec.columnE
        , columnH = rec.columnF
       where name = rec.name
       ;
      end loop;
    end;
 /

Unable to extend temp segment by 128 in tablespace TEMP. Another option to execute that query?

1 Answers