[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4559
  • Last Modified:

Oracle 10g Importing Documenrum Data Error - ORA-01659: unable to allocate MINEXTENTS

Hi,

I was trying to import one table from oracle dmp file im getting this “ORA-01659: unable to allocate MINEXTENTS”  error please have a look full import process and let me know if  im doing something wrong ??
And my exported database has some Arabic data also other then this table I imported when I checked the data it was showing boxes.

How can I get logs also while importing.


[CONSOLE ]
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Import file: EXPDAT.DMP > D:\ 06september.dmp
Enter insert buffer size (minimum is 8192) 30720> 677777
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SCSUSER, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: myCoNmae
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: DM_AUDITTRAIL_S
Enter table(T) or partition(T:P) name or . if done:

. importing MYCONMAE's objects into MYCONMAE

IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE TABLE "DM_AUDITTRAIL_S" ("R_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,"
 " "EVENT_NAME" VARCHAR2(64) NOT NULL ENABLE, "EVENT_SOURCE" VARCHAR2(64) NOT"
 " NULL ENABLE, "R_GEN_SOURCE" NUMBER(10, 0) NOT NULL ENABLE, "USER_NAME" VAR"
 "CHAR2(32) NOT NULL ENABLE, "AUDITED_OBJ_ID" VARCHAR2(16) NOT NULL ENABLE, ""
 "TIME_STAMP" DATE NOT NULL ENABLE, "STRING_1" VARCHAR2(200) NOT NULL ENABLE,"
 " "STRING_2" VARCHAR2(200) NOT NULL ENABLE, "STRING_3" VARCHAR2(200) NOT NUL"
 "L ENABLE, "STRING_4" VARCHAR2(200) NOT NULL ENABLE, "STRING_5" VARCHAR2(200"
 ") NOT NULL ENABLE, "ID_1" VARCHAR2(16) NOT NULL ENABLE, "ID_2" VARCHAR2(16)"
 " NOT NULL ENABLE, "ID_3" VARCHAR2(16) NOT NULL ENABLE, "ID_4" VARCHAR2(16) "
 "NOT NULL ENABLE, "ID_5" VARCHAR2(16) NOT NULL ENABLE, "CHRONICLE_ID" VARCHA"
 "R2(16) NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(255) NOT NULL ENABLE, "VERSI"
 "ON_LABEL" VARCHAR2(16) NOT NULL ENABLE, "OBJECT_TYPE" VARCHAR2(32) NOT NULL"
 " ENABLE, "EVENT_DESCRIPTION" VARCHAR2(64) NOT NULL ENABLE, "POLICY_ID" VARC"
 "HAR2(16) NOT NULL ENABLE, "CURRENT_STATE" VARCHAR2(64) NOT NULL ENABLE, "WO"
 "RKFLOW_ID" VARCHAR2(16) NOT NULL ENABLE, "SESSION_ID" VARCHAR2(16) NOT NULL"
 " ENABLE, "USER_ID" VARCHAR2(16) NOT NULL ENABLE, "OWNER_NAME" VARCHAR2(32) "
 "NOT NULL ENABLE, "ACL_NAME" VARCHAR2(32) NOT NULL ENABLE, "ACL_DOMAIN" VARC"
 "HAR2(32) NOT NULL ENABLE, "APPLICATION_CODE" VARCHAR2(64) NOT NULL ENABLE, "
 ""CONTROLLING_APP" VARCHAR2(32) NOT NULL ENABLE, "ATTRIBUTE_LIST" VARCHAR2(2"
 "000) NOT NULL ENABLE, "ATTRIBUTE_LIST_ID" VARCHAR2(16) NOT NULL ENABLE, "AU"
 "DIT_SIGNATURE" VARCHAR2(255) NOT NULL ENABLE, "AUDIT_VERSION" NUMBER(10, 0)"
 " NOT NULL ENABLE, "HOST_NAME" VARCHAR2(128) NOT NULL ENABLE, "TIME_STAMP_UT"
 "C" DATE NOT NULL ENABLE, "I_AUDITED_OBJ_CLASS" NUMBER(10, 0) NOT NULL ENABL"
 "E, "REGISTRY_ID" VARCHAR2(16) NOT NULL ENABLE, "I_IS_ARCHIVED" NUMBER(6, 0)"
 " NOT NULL ENABLE, "AUDITED_OBJ_VSTAMP" NUMBER(10, 0) NOT NULL ENABLE, "I_IS"
 "_REPLICA" NUMBER(6, 0) NOT NULL ENABLE, "I_VSTAMP" NUMBER(10, 0) NOT NULL E"
 "NABLE, "ATTRIBUTE_LIST_OLD" VARCHAR2(1000))  PCTFREE 10 PCTUSED 40 INITRANS"
 " 1 MAXTRANS 255 STORAGE(INITIAL 2819620864 FREELISTS 1 FREELIST GROUPS 1 BU"
 "FFER_POOL DEFAULT) TABLESPACE "DM_MYCONMAE_DOCBASE" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1659 encountered

ORA-01659: unable to allocate MINEXTENTS beyond 10 in tablespace DM_MYCONMAE_DOCBAS
E

Import terminated successfully with warnings.
C:\>

0
xeondxb
Asked:
xeondxb
4 Solutions
 
Peter KwanCommented:
This means the tablespace is not large enough to allocate the required space. You may follow the following to resolve the problem:

http://halimdba.blogspot.com/2011/04/ora-1659-unable-to-allocate-minextents.html
0
 
Javier MoralesOracle DBACommented:
Try to repeat the export with the option COMPRESS=NO
By default, exp "compresses" the tables to only one extension.

If table was size 20Gb and you have 10 datafiles of 5Gb each one in your source database, you will get that error message unless you use the COMPRESS=NO parameter.

Table may have 100 extensions (pieces), that imp will try to recreate using only one.

Cheers,
Javier
0
 
OP_ZaharinCommented:
- use the following query to check for the available space for each tablespace you have. it will shows that tablespace DM_MYCONMAE_DOCBASE is 100% or almost:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

- if you did not find the method to extent the database from the earlier expert link, use the following query to find the datafile location of the DM_MYCONMAE_DOCBASE tablespace:
 
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY FROM DBA_DATA_FILES;


- then extent the size of the tablespace to greater than the existing size:
ALTER DATABASE DATAFILE ‘c:\oracle\oradata\datafile.dbf’ RESIZE 800M;

- or add a new datafile to that tablespace:
ALTER TABLESPACE DM_MYCONMAE_DOCBASE ADD DATAFILE 'c:\oracle\oradata\datafile.dbf' SIZE 800M AUTOEXTEND ON NEXT 512K MAXSIZE 1000M;
0
 
slightwv (䄆 Netminder) Commented:
An alternative is to precreate the tables.  Take a look at the table that errored:  INITIAL 2819620864

This will try to allocate a single extent of that size in the new database.  COMPRESS=N suggested above will help with this but requires another export.

If you pre-create the tables using whatever parameters you want and use IGNORE=Y on import everything should be fine.

You can generate a create script from the import with the INDEXFILE parameter.  The resulting file will have the create table SQL commented out but that is a quick find/replace fix.  Then tweak the size values/tablespaces/??? and run the script.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now