3

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?

  • 1
    You should show us where tablespace_name = 'UNDOTBS1' and not where tablespace_name like '%UNDO%' – miracle173 Mar 11 '18 at 10:07
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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.