{"id":62,"date":"2009-03-09T22:10:57","date_gmt":"2009-03-10T02:10:57","guid":{"rendered":"http:\/\/dbtricks.com\/?p=62"},"modified":"2009-03-09T22:15:15","modified_gmt":"2009-03-10T02:15:15","slug":"restart-data-pump-after-import-errors","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=62","title":{"rendered":"Restart Data Pump after import errors"},"content":{"rendered":"

When using Data Pump, you may encounter the following error:
\nORA-39171: Job is experiencing a resumable wait.<\/strong>
\nTogether with<\/p>\n

ORA-01653: Unable to extend table %s.%s by %s in tablespace %s <\/strong>
\nOr
\nORA-01691\u00a0 unable to extend lob segment string.string by string in tablespace string<\/strong>
\nBoth errors basically say that the table space you are using can not allocate more space. This could be because the datafile is full, the disk is full or autoextend is not set. This is simple to check by running the following query on DBA_DATA_FILE:
\nselect *
\nfrom dba_data_files
\nwhere tablespace_name=’TABLESPACE_NAME’
\n<\/strong>
\nThe bytes column will tell you how many bytes are used by Oracle in the tablespace. If AUTOEXTENSIBLE is set to NO, then Oracle will not extend the size of the tablespace.<\/p>\n

In many cases, the Data Pump import may seem to hang. Data Pump will try to continue after some time but unless something is changed, you will keep getting the above errors.<\/p>\n

The solution, however, is simple enough. All you need to do is use the Data Pump Restart Capability:
\n1)\u00a0\u00a0 \u00a0In the IMPDP window, click CTRL-C to stop the job.
\n2)\u00a0\u00a0 \u00a0In the command line type:
\nImport> stop_job=immediate<\/strong>
\n3)\u00a0\u00a0 \u00a0Use SQLPlus to make the required changes to the table space. You can:<\/p>\n

    \na)\u00a0\u00a0 \u00a0Add DataFile ALTER TABLESPACE <tablespace name> ADD DATAFILE ‘<path to file name and\u00a0 file name>’ SIZE <integer>M;<\/p>\n

    b)\u00a0\u00a0 \u00a0Resize the Datafile:
    \nALTER DATABASE DATAFILE ‘< path to file name and\u00a0 file name >’ RESIZE <integer>M;
    \nc)\u00a0\u00a0 \u00a0 Enable autoextend:
    \nALTER DATABASE DATAFILE ‘< path to file name and\u00a0 file name >’ AUTOEXTEND ON MAXSIZE UNLIMITED;<\/ol>\n

    4)\u00a0\u00a0 \u00a0Attach the Job.
    \n>impdp system\/manager attach=Job_Name<\/strong>
    \nIf you did not provide a specific job name when you first ran the IMPDP, Oracle will assign a default name for it. In order to find the System assigned name for the IMPDP job you can run the following query:
    \n SELECT * FROM DBA_DATAPUMP_JOBS;<\/strong>
    \nThe result will probably be something like SYS_IMPORT_FULL_number.
    \n5)\u00a0\u00a0 \u00a0 Restart the job
    \nImport> start_job<\/strong>
    \n6)\u00a0\u00a0 \u00a0You can check the status of the job by simply typing STATUS in the utility command prompt.
    \n Import> status<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

    When using Data Pump, you may encounter the following error: ORA-39171: Job is experiencing a resumable wait. Together with ORA-01653: Unable to extend table %s.%s by %s in tablespace %s Or ORA-01691\u00a0 unable to extend lob segment string.string by string in tablespace string Both errors basically say that the table space you are using can […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[42,43,38,48,40,45,46,47,107],"class_list":["post-62","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-data-pump","tag-expdp","tag-export","tag-impdb","tag-import","tag-ora-01653","tag-ora-01691","tag-ora-39171","tag-oracle"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/62","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=62"}],"version-history":[{"count":2,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions"}],"predecessor-version":[{"id":64,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions\/64"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}