Andrew Fraser DBA

Fix for datapump import ORA-01658: unable to create initial extent for segment in tablespace

Datapump errors:

ORA-39171: Job is experiencing a resumable wait.
ORA-01658: unable to create INITIAL extent for segment in tablespace

If doing an import including data, then fix is obvious: just need to add more space.

But if doing a metadata only import to bring over empty tables, you don’t want to waste a lot of empty space on those.

Fix in that case is to instruct import to leave out the storage parameters by specifying this impdp parameter:

transform=storage:n

2) That fixes most of the space issues, but if you have a lot of partitioned tables or indexes, be aware that they create with 8mb extents by default, which soon adds up.
You can change that behaviour by having target tablespaces with small 64k uniform extent size:

CREATE TABLESPACE ... EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64k ... ;

Script to generate commands

SELECT 'CREATE TABLESPACE '
       || LOWER ( tablespace_name )
       || ' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64k DATAFILE ''/path/here/'
       || LOWER ( tablespace_name )
       || '_0001.dbf'' SIZE 1m AUTOEXTEND ON NEXT 10m MAXSIZE 512m ;'
  FROM dba_tablespaces
 WHERE tablespace_name NOT IN (
       'SYSAUX'
     , 'SYSTEM'
     , 'TEMP'
     , 'UNDOTBS'
       )
 ORDER BY 1
;