Import Oracle .dmp with error 1659, unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM

exportimportoracle

I'm trying to importing an Oracle DUMP file (v10.2) into Oracle Express 11.2.0.2.0. I was getting some schema errors and resoled them by following other posts here. Now I'm getting this error and I'm not able to understand it enough to troubleshoot it:

C:\>imp system/pass FROMUSER=DWH TOUSER=IMPORT_MAN file=e:\LRT_ARCHIVE_170911.dmp

Import: Release 11.2.0.2.0 - Production on Wed Sep 20 11:01:48 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by DWH, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing DWH's objects into IMPORT_MAN
IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE TABLE "LRT_ARCHIVE_170911" ("EVENTTIME" TIMESTAMP (6) WITH LOCAL TIM"
 "E ZONE, "TTR_ID" NUMBER(10, 0), "STATUS" VARCHAR2(3), "ISEMPTY" VARCHAR2(1)"
 ", "TDP_IDCURR" NUMBER(6, 0), "TNP_IDLASTPOS" NUMBER(6, 0), "STORINGREASON" "
 "VARCHAR2(20), "ISPROCESSED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 "
 "MAXTRANS 255 STORAGE(INITIAL 1824522240 FREELISTS 1 FREELIST GROUPS 1 BUFFE"
 "R_POOL DEFAULT)                   LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 3 in tablespace SYSTEM
Import terminated successfully with warnings.

Would really appreciate if someone can point out why this is happening and how to resolve it.

Also, I was running the command with the full=yes command-arg but that was not running at all and reported that I cannot use full with FROMUSER

Background:
This is a freshly installed instance of Oracle Express. I got the dump and am tasked to restore it. When I tried originally, I followed the warning about a schema/user that actually created/exported the dump so I created a new user with same name DWH. I think that was a non-issue. Apart from that, I have not done much in the two hours I have spent on this.

Best Answer

01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE

As stated above you have two options, either increase the size of your SYSTEM tablespace, which might be easier or first extract the table DDL(CONTENT=METADATA_ONLY) from the dump file and alter the DDL script to change MINEXTENT to lower value and run the script to create tables then import the data(CONTENT=DATA_ONLY).

From comment...

I'm afraid I don't. I do have this query in my clipboard history: ALTER TABLESPACE ADD DATAFILE but don't actually know how to use it or why is the SYSTEM schema being used here when I created the IMPORT_MAN user/schema

The SYSTEM mentioned in your error is a tablespace, not a schema. SYSTEM tablespace is used to store metadata of system and user objects such as tables under SYS schema. So, while importing the tables you need to have space on SYSTEM tablespace as well as in user's default tablespace where it stores the rows. To add datafiles, refer to this link ALTER TABLESPACE

Related Question