Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Question

I'm using IMPDP with the "network_link" for perform export/import in a single-pass.

It runs fine until I get this error.

ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Here are the datafiles for the UNDO tablespace:

oracle@ORA3> select bytes/1024/1024/1024, maxbytes/1024/1024/1024
             from dba_data_files 
             where tablespace_name like '%UNDO%';

Output:

BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024
-------------------- -----------------------
          31.9999847              31.9999847
          31.9999847              31.9999847
          31.9999847              31.9999847
                   2              31.9999847
                   2              31.9999847
                   2              31.9999847

So I have around 196G of undo space. I added the last three data files after the error.

The largest tablespace is 674G. Do I need to make the UNDO tablespace larger than this? Or is there something else I should do?

Answer 1

Ensure that the default undo tablespace has enough capacity.

Based on your output, I see 3 tablespaces with 2GB of size. Perhaps one of them is the default assigned UNDO and auto extend is off.

Another prediction is that the default undo could be another tablespace with a different name, the query executed does not indicate the default undo but rather checks the tablespaces named with 'UNDO'.

You can check the default undo tablespace using the below query

select value from v$parameter where name like 'undo_tablespace';