OraFAQ Forum: Server Utilities » importing records

Home » RDBMS Server » Server Utilities » importing records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

importing records [message #277721]

Wed, 31 October 2007 05:30

varu123
Messages: 754
Registered: October 2007

Senior Member

Hi Michel,
I am importing millions of records.
I have created the necessary tablespace with autoextend on option.

During import i am getting folowing error:
ora-00017
thanks

Report message to a moderator

Re: importing records [message #277723 is a reply to message #277721]

Wed, 31 October 2007 05:34

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ This is not AskMichel, ask the whole community
2/ Provide all useful information (read guidelines)
3/

IMP-00017: following statement failed with ORACLE error %lu:
 *Cause:  Import failed to execute the statement from the export file
          because of an Oracle error.
 *Action: Look up the accompanying Oracle message in the ORA message
          chapters of this manual and take appropriate action.

Regards
Michel

Report message to a moderator

Re: importing records [message #277724 is a reply to message #277721]

Wed, 31 October 2007 05:34

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

ORA-00017:	session requested to set trace event
Cause:	The current session was requested to set a trace event by another session.
Action:	This is used internally; no action is required.

Report message to a moderator

Re: importing records [message #277727 is a reply to message #277721]

Wed, 31 October 2007 05:36

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

This is not AskMichel, ask the whole community

That's true, but Michel is leading the whole community.

Report message to a moderator

Re: importing records [message #277730 is a reply to message #277727]

Wed, 31 October 2007 05:38

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Michel is leading the whole community

I disagree.
OraFAQ lived long before I came and will live long after I'll leave.

Regards
Michel

Report message to a moderator

Re: importing records [message #277738 is a reply to message #277721]

Wed, 31 October 2007 05:48

varu123
Messages: 754
Registered: October 2007

Senior Member

In the alert log i found ora-1652: unable to extend temp segment by 388548 in tablespace rpli.

Duirng the import when large table comes it shows the error.

Report message to a moderator

Re: importing records [message #277740 is a reply to message #277738]

Wed, 31 October 2007 05:50

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Then enlarge your tablespace.

Regards
Michel

Report message to a moderator

Re: importing records [message #277746 is a reply to message #277721]

Wed, 31 October 2007 06:21

varu123
Messages: 754
Registered: October 2007

Senior Member

I enlarged my tablespace. When i ran import there were some tables that failed to get created .Now iam trying to import them individually but getting the error:

IMP-00017: following statement failed with ORACLE error 1658:
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace RPLI

Report message to a moderator

Re: importing records [message #277747 is a reply to message #277721]

Wed, 31 October 2007 06:25

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Give quota unlimited to the intended user to the intended tablespace if you have already enlarge your tablespace up to the mark.

Report message to a moderator

Re: importing records [message #277751 is a reply to message #277721]

Wed, 31 October 2007 06:46

varu123
Messages: 754
Registered: October 2007

Senior Member

Quota is unlimited but still the problem.
The create table statements in import have minextents ,maxextents pctincrease options.
I created the tablespace with autoextend on option.
Do i need to create the tabelspace with options as in the create table statements seen during import?

Report message to a moderator

Re: importing records [message #277752 is a reply to message #277746]

Wed, 31 October 2007 06:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First, try to export with option "compress=n".
If this does not work then enlarge your tablespace once more.

Regards
Michel

Report message to a moderator

Re: importing records [message #277754 is a reply to message #277721]

Wed, 31 October 2007 06:51

varu123
Messages: 754
Registered: October 2007

Senior Member

we are importing not exporting

Report message to a moderator

Re: importing records [message #277755 is a reply to message #277721]

Wed, 31 October 2007 06:54

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

maxsize unlimited +autoextend on

Report message to a moderator

Re: importing records [message #277763 is a reply to message #277754]

Wed, 31 October 2007 07:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes you are importing but to prevent from this error you have to use this option in EXPORT.
If you can't exoprt again then the only solution is to enlarge the tablespace.
As you don't post the full message we don't know what is the size of the initial extent it can't create. So it is on you to know how to enlarge the tablespace.

Regards
Michel

Report message to a moderator

Re: importing records [message #277771 is a reply to message #277746]

Wed, 31 October 2007 08:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

varu123 wrote on Wed, 31 October 2007 07:21

Then add more space to the tablespace (or do what Michel has suggested). You do not have enough contiguous space to create the INITIAL extent, which may be the size of the entire table if you used COMPRESS=Y, which is the default in 10gR2.

Report message to a moderator

Re: importing records [message #278046 is a reply to message #277721]

Thu, 01 November 2007 11:27

varu123
Messages: 754
Registered: October 2007

Senior Member

I increased the tablespace size,rollback segment size and issue alter tablespace coalesce statement .But there are some tables that give the same error every time i try to import them.

Report message to a moderator

Re: importing records [message #278048 is a reply to message #278046]

Thu, 01 November 2007 11:31

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

We can't help you more.
All informations are in the topic.
Just follow them.

Once again check the value for initial extent we can't see over your shoulders.

Regards
Michel

[Updated on: Thu, 01 November 2007 11:32]

Report message to a moderator

Re: importing records [message #278050 is a reply to message #277721]

Thu, 01 November 2007 11:48

varu123
Messages: 754
Registered: October 2007

Senior Member

Ok, i checked the create table statements during import. They have initial ,next ,minextent values different from values in the tablespace i created for that user.
Is this difference causing the problem?
Do i have to drop and recreate the tablespace with storage clauses as mentioned in the create table statement?
If yes than why import for other tables is working?

Report message to a moderator

Re: importing records [message #278052 is a reply to message #278050]

Thu, 01 November 2007 11:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No. No.

Regards
Michel

Report message to a moderator

Re: importing records [message #278058 is a reply to message #277721]

Thu, 01 November 2007 12:17

varu123
Messages: 754
Registered: October 2007

Senior Member

There are five tables that are giving this error.Rest of the tables got imported well.
I think i have to manually create these tables and then insert data.

Report message to a moderator

Re: importing records [message #278062 is a reply to message #278058]

Thu, 01 November 2007 12:26

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

And why do you think the results will be any different ?

Report message to a moderator

Re: importing records [message #278063 is a reply to message #278058]

Thu, 01 November 2007 12:31

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I think i have to manually create these tables and then insert data.

This a way to do it... if you don't use the same statement as import.

Regards
Michel

Report message to a moderator

Re: importing records [message #278064 is a reply to message #277721]

Thu, 01 November 2007 12:32

varu123
Messages: 754
Registered: October 2007

Senior Member

I dont think but they are.
I created one table. Initially it gave the same error. Then i issued alter tablespace coalesce statement, it worked.

I issued this statement during import also but no success.

Report message to a moderator

Re: importing records [message #278069 is a reply to message #278050]

Thu, 01 November 2007 12:54

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

varu123 wrote on Thu, 01 November 2007 12:48

Ok, i checked the create table statements during import. They have initial ,next ,minextent values different from values in the tablespace i created for that user.

Of course, because those values for the objects. It has nothing to do with what values the tablespace has.
Either pre-create your objects in a locally managed tablespace or don't be stingy with adding space to the tablespace.
You still haven't told us what the INITIAL and NEXT values are for the create table statement. What's the secret? Why don't you provide all the helpful information up front so the thread doen't have to go to 20+ messages/

Report message to a moderator

Re: importing records [message #278072 is a reply to message #277721]

Thu, 01 November 2007 13:16

varu123
Messages: 754
Registered: October 2007

Senior Member

The initial extent size is 750m,next is 450m as appear in import.The original create table statements do not have any storage clauses.

Report message to a moderator

Re: importing records [message #278241 is a reply to message #278072]

Fri, 02 November 2007 08:29

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

varu123 wrote on Thu, 01 November 2007 14:16

The initial extent size is 750m,next is 450m as appear in import.The original create table statements do not have any storage clauses.

Then your tablespace needs to have a contiguous chunk of 750M in order to create the object, even if no rows are to be inserted.

Report message to a moderator

Re: importing records [message #278318 is a reply to message #277721]

Fri, 02 November 2007 21:09

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

extent size is 750m,next is 450m

!!!

Report message to a moderator

Previous Topic:	SQL Loader
Next Topic:	second enclosure string not found

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:55:25 CDT 2024