unable to extend segment by 8 in undo tablespace 'UND_TBS'

Question

I'm trying to insert data from table1 to an existing table say table2. table1 has 30 million records. Used the below command to insert the data. Got the given error after some time.

insert into TABLE2 (select * from TABLE1)
Error at Command Line:31 Column:0
Error report:
SQL Error: ORA-30036: unable to extend segment by 8 in undo tablespace 'UND_TBS'
30036. 00000 -  "unable to extend segment by %s in undo tablespace '%s'"

It shown that, the specified undo tablespace has no more space available.

Before retrying the operation, what can I do. Is there any other workarounds available to insert the data successfully?

Answer 1

As @a_horse_with_no_name commented out,

I have created a new data file keeping the autoextend on.

alter tablespace UND_TBS add datafile   '/path/my_data_file.dbf' size 7168M autoextend on;

The path can be identified by,

select file_name from dba_data_files where tablespace_name ='UND_TBS';

Ans you can get the maximum/free size of the tablespace by,

SELECT b.tablespace_name,
  tbs_size SizeMb,
  a.free_space FreeMb
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes)/1024/1024 ,2) AS free_space
  FROM dba_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name,
    SUM(bytes)/1024/1024 AS tbs_size
  FROM dba_data_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name(+)=b.tablespace_name;