Solved

IMP-00003: ORACLE error 1658 encountered

Posted on 2012-04-10
8
2,660 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
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:
praveencpk earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 68

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now