OraFAQ Forum: Server Utilities » Import Successful with warnings....ORA-01658 error..

Home » RDBMS Server » Server Utilities » Import Successful with warnings....ORA-01658 error..

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

Import Successful with warnings....ORA-01658 error.. [message #327153]

Sat, 14 June 2008 06:19

sathyguy
Messages: 31
Registered: January 2006

Member

Friends,

OS: RHEL AS 3.0
DB: Oracle 9iR2

When i try to import the dump, i am getting the below message.
"Import was successful with warnings"
when i check the log file there is

Quote:

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 PROD_DEFAULT

Please check the below, this is what i did before importing.
I want to import only the table structures without rows from the user "SMS" to "NMS".
After creating the below tablespace the free space in "/u02" is 3GB.
AM i have to change any value in the below statement?

Quote:

CREATE TABLESPACE "PROD_DEFAULT" DATAFILE
'/u02/oradata/PROD_DEFAULT.dbf' SIZE 3072M REUSE
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

Quote:

CREATE TEMPORARY TABLESPACE "PROD_TEMP" TEMPFILE
'/u02/oradata/PROD_TEMP.dbf' SIZE 3072M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

Quote:

CREATE USER "NMS" IDENTIFIED BY "SDF345345KLSDSD8K" DEFAULT
TABLESPACE "PROD_DEFAULT"
TEMPORARY TABLESPACE "PROD_TEMP"

Quote:

GRANT CONNECT,RESOURCE TO NMS

Quote:

[oracle@linuxdb oracle]$ imp system/password file=/u02/today1.dmp
log=/u02/today1.log fromuser=sms touser=nms rows=n buffer=8000000 commit=y

Note:
Even if i omit "buffer" and "commit" the same error occurs.

where im making a mistake? Is there any changes i have to made in the create tablespace statement.
Please point out the mistake.

thanks & regards

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327170 is a reply to message #327153]

Sat, 14 June 2008 08:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I would add onto the command line IGNORE=YES
You will likely still get the warning but the data will get imported.

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327173 is a reply to message #327170]

Sat, 14 June 2008 08:23

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Quote:

ORA-01658: unable to create INITIAL extent for segment in tablespace string

Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

Babu

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327177 is a reply to message #327170]

Sat, 14 June 2008 09:04

sathyguy
Messages: 31
Registered: January 2006

Member

"You will likely still get the warning but the data will get imported."
i don't want the data to be imported i need only the table structures thats why im using rows=n. is rows=n means no data will be imported? please clarify me.

gentlebabu:
"Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL "

only for the table structures i have to add another tablespace?
Adding additional tablespace is ok but how to set the smaller values for INITIAL?
Can you give me any sample statement?

thanks

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327179 is a reply to message #327153]

Sat, 14 June 2008 09:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I previously did not notice ROWS=NO.
You will get the desired results with INGNORE=YES

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327182 is a reply to message #327179]

Sat, 14 June 2008 09:50

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

I agree; but you did one small mistake. Please post output of below query


select sum(bytes) from dba_data_files where tablespace_name = ( 
select DEFAULT_TABLESPACE from dba_users where username='SMS')
/

Babu

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327189 is a reply to message #327177]

Sat, 14 June 2008 10:28

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

Senior Member
Account Moderator

Quote:

only for the table structures i have to add another tablespace?
Adding additional tablespace is ok but how to set the smaller values for INITIAL?
Can you give me any sample statement?

Import with indexfile, modify the storage clause in the generated file and execute it with SQL*Plus.

Regards
Michel

[Updated on: Sun, 15 June 2008 07:30]

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327221 is a reply to message #327189]

Sun, 15 June 2008 06:36

sathyguy
Messages: 31
Registered: January 2006

Member

gentlebabu,

Quote:

SQL> select sum(bytes) from dba_data_files where tablespace_name = (
2 select DEFAULT_TABLESPACE from dba_users where username='SMS');

SUM(BYTES)
----------
3221225472

SQL>

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327223 is a reply to message #327221]

Sun, 15 June 2008 07:31

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

Senior Member
Account Moderator

The answer is in my post, your tablespace size is meaningless if you use it.

Regards
Michel

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327225 is a reply to message #327223]

Sun, 15 June 2008 07:51

sathyguy
Messages: 31
Registered: January 2006

Member

michel,
i imported with indexfile='/u02/idf.sql'...
All are table creation statements with "REM"
what should i change?
there is a storage clause like....

Quote:

STORAGE(INITIAL 6569984 FREELISTS 1 FREELIST GROUPS 1)

Please tell me what should i change in the above.
i forget to tell one more information...
actually i want to import "NMS_DEFAULT" tablespace's table structures to "PROD_DEFAULT" tablespace.
also I want to import only the table structures without rows from the user "SMS" to "NMS".

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327236 is a reply to message #327225]

Sun, 15 June 2008 09:32

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

Senior Member
Account Moderator

Quote:

All are table creation statements with "REM"
what should i change?

Remove REM.

Quote:

there is a storage clause like....
Please tell me what should i change in the above.

Change them to what you want.

Quote:

actually i want to import "NMS_DEFAULT" tablespace's table structures to "PROD_DEFAULT" tablespace.

So change it in indexfile.

Quote:

I want to import only the table structures without rows from the user "SMS" to "NMS".

Change the owner in indexfile when it exists and execute the file with the final owner.

Regards
Michel

Report message to a moderator

Re: Import Successful with warnings....ORA-01658 error.. [message #327376 is a reply to message #327236]

Mon, 16 June 2008 04:42

sathyguy
Messages: 31
Registered: January 2006

Member

thank you so much....
i got it.....
your number of post tells your experience......

[Updated on: Mon, 16 June 2008 05:21]

Report message to a moderator

Previous Topic:	External table selection problem
Next Topic:	SQLLDR doesn't work when last columns are not null

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 03:38:29 CDT 2024