IMP-00003: ORACLE error 1658 encountered

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
YBSolutionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YBSolutionsAuthor Commented:
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
YBSolutionsAuthor Commented:
Just FYI,

I have changed table name and tablespace name.
0
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Wasim Akram ShaikCommented:
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
slightwv (䄆 Netminder) Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
YBSolutionsAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.