CODEWITHSUNDEEP
oracle-databasetablespace
jartymcfly
jartymcfly

Reputation: 2005

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

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?

Upvotes: 1

Views: 1367

Answers (1)

schurik
schurik

Reputation: 7928

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;
 /

Upvotes: 2

Related Questions