Failed to expand segment by 8 in undo tablespace 'UND_TBS'

Question

I am trying to insert data from table1into an existing table, say table2. table1 has 30 million records. The following command is used to insert data. Got the given error after a while.

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 has been shown that the specified undo tablespace has no more space available.

Before repeating the operation, what should I do. Are there any other workarounds available to insert data successfully?

Answer 1

As @a_horse_with_no_name is commented out ,

I created a new data file supporting autoextend.

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

Path can be identified

select file_name from dba_data_files where tablespace_name ='UND_TBS';

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

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;