?
Solved

IMP-00003: ORACLE error 1658 encountered

Posted on 2012-04-10
8
Medium Priority
?
3,223 Views
Last Modified: 2012-04-11
I am getting below error, while trying to import
=======

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path

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

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses AL32UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
. importing USER's objects into USER
IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE INDEX "CUST_BIRTHDATE_AK1" ON "CUST" ("BIRTHDATE" , "CUST_ID" ,"
 " "HISTORY_VERSION_NUMBER" )  PCTFREE 10 INITRANS 17 MAXTRANS 255 STORAGE(IN"
 "ITIAL 4294967294 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BU"
 "FFER_POOL DEFAULT) TABLESPACE "TABLESPACE" LOGGING"
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 28 in tablespace TABLESPACE
IMP-00017: following statement failed with ORACLE error 1659:
 "CREATE INDEX "CUST_ACCT_DOC_ACCT_DOC_FK" ON "CUST_ACCT_DOC" ("ACCT_DOC_ID" "
 ")  PCTFREE 10 INITRANS 17 MAXTRANS 255 STORAGE(INITIAL 982515712 NEXT 10485"
 "76 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
 "CE "TABLESPACE" LOGGING"
IMP-00003: ORACLE error 1659 encountered
0
Comment
Question by:YBSolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:YBSolutions
ID: 37830612
I took this dump from prod (without data) and trying to import on laptop. I know it is related to storage, therefore, have tried below options.

1. I have tried to allocate more space under tablespaces (size..autoextend on next ..maxsize...)
2. Tried creating bigfile tablespace(with 50g)
3. Tried to create sqlfile from dump and run it (by changing some storage values) not all.

Note: I think if I will remove the storage parameters (for indexes and tables) from sqlfile (created from dump) than it should work. But it is around 15K lines code.

Do you know if there is any solution for it?
0
 

Author Comment

by:YBSolutions
ID: 37830617
Just FYI,

I have changed table name and tablespace name.
0
 
LVL 12

Accepted Solution

by:
Praveen Kumar Chandrashekatr earned 1000 total points
ID: 37830922
>>Warning: the objects were exported by SYS, not by you<<
it states that the export was taken by sys user and you are trying to import using different user.

also before importing the getting tablespace used size from your production and try to create the tablespace with little more then the specified space.

 SELECT TABLESPACE_NAME TBSP_NAME , USED_SPACE, TABLESPACE_SIZE TBSP_SIZE, USED_PERCENT FROM SYS.DBA_TABLESPACE_USAGE_METRICS;
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37831428
The cause of the error is due to Storage Clause parameters..

The OERR utility shows this..

ORA-01659: unable to allocate MINEXTENTS beyond string in tablespace string
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 you had mentioned that you already had added some tablespace.. but have specified the storage clauses to the data file when you had added..

do these steps

alter tablespace <tablespace_name> coalesce;

also add a data file with minimum extents ie.
alter tablespace <tablespace_name> add datafile <data_file_path> min extents <10> max extents unlimited;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37832204
Since you are just wanting to create a test databsae that is smaller from production the suggestions above will not work since they are asking you to create a database large enough to handle the production tables.

If you just want to create the tables, exp/imp is probably not the best approach here.  The only way I know of is what you have already mentioned:  Create a script form the export and edit it to remove the large storage values.

If you have a decent text editor if shouldn't be that bad to search for STORAGE and delete to the end of the statement.

If I can suggest an alternative:  DBMS_METADATA.  You can tell it to not grab the sotrage parameters:
http://dbaforums.org/oracle/index.php?showtopic=1689
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 37832572
It might also be a good idea not to export ALL tables. It is easy to write a small PL/SQL script providing you with the list of tables excluding some, which aren't necessary for your tests. That's what we are doing, since we have a handful of really big tables, and the important remainder is about 10% of DB size only.
0
 

Author Closing Comment

by:YBSolutions
ID: 37833336
Space under the tablespaces was being used and they were getting full, how ever there was not even a single row of data. While creating the object oracle was allocating the space (physical) to object. <br />We imported the same in 11.2 database with just 200M tablespaces size (2 tablespace) and it went fine.<br />New feature/parameter 'DEFERRED_SEGMENT_CREATION<wbr />'  in oracle 11.2 is taking care this issue. <br />If set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.<br />For more details please see the below links:<br /><br />http://docs.oracle.com/cd/<wbr />E14072_01/<wbr />server.112<wbr />/e10820/in<wbr />itparams06<wbr />9.htm<br /><br />http://tkyte.blogspot.co.u<wbr />k/2011/02/<wbr />deferred-s<wbr />egment-cre<wbr />ation.html<wbr />
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question