0

I am getting these 2 statements while importing dump into orcle:

ORA-39171: Job is experiencing a resumable wait. 
ORA-01653: unable to extend table DATA_TABLE  by 8192 in tablespace TABLE_DATA

Please let me know the solution if you know.

alexander.polomodov
  • 5,396
  • 14
  • 39
  • 46
sagar
  • 21
  • 1
  • 2
  • Possible duplicate of [ORA-01653: unable to extend table by in tablespace ORA-06512](http://stackoverflow.com/questions/27129376/ora-01653-unable-to-extend-table-by-in-tablespace-ora-06512) – Mat Mar 09 '16 at 17:58

2 Answers2

3

When you run out of tablespace, impdp very politely pauses and informs you that you need to expand the tablespace. Once you have done so, impdp continues the import without additional intervention.

In your case, you need to expand tablespace TABLE_DATA. Log into another session, and either up the limit on the files associated with TABLE_DATA, or add a file. Say your data file is table_data_01.DBF and its max size is 500M. You could expand it to 1G with the following:

ALTER DATABASE 
  DATAFILE 'D:\ORACLE\ORADATA\xxxx\table_data_01.DBF  AUTOEXTEND ON
  NEXT 256M
  MAXSIZE 1G;

If you wanted to add another data file to the tablespace, you could do so with the following:

ALTER TABLESPACE TABLE_DATA
  ADD DATAFILE 'D:\ORACLE\ORADATA\xxxx\CIS_DATA_02.DBF'
  SIZE 256M
  AUTOEXTEND ON
  NEXT 16M
  MAXSIZE 1G;

Be sure to adjust the paths and sizes as appropriate for your environment.

Brian Leach
  • 2,025
  • 1
  • 11
  • 14
0

Whereas the above does work, when using Oracle 12c (12.1.0.2.0) I left the import in it's resumable state and opened another sqlplus window. From there, I ran:

ALTER TABLESPACE DATA1 ADD DATAFILE
SIZE 52428800 AUTOEXTEND ON NEXT 8388608 MAXSIZE 30720M;

This added a fourth datafile to our tablespace and created the name since it is oracle managed filenames. Upon creation of the datafile the Oracle import immediately continued from it's resumable state. Of course, if you are uncomfortable with that import, simply drop the schema and do an entire re-import. The new tablespace that you created will still be there so you will not run into the Resumable wait error again. Note also that the parameters for SIZE, AUTOEXTEND ON NEXT and MAXSIZE are different because I used the same parameters as our database files already in the tablespace.

Mark Grimes
  • 1