Home » RDBMS Server » Server Utilities » Import Successful with warnings....ORA-01658 error..
Import Successful with warnings....ORA-01658 error.. [message #327153] Sat, 14 June 2008 06:19 Go to next message
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
Re: Import Successful with warnings....ORA-01658 error.. [message #327170 is a reply to message #327153] Sat, 14 June 2008 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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.
Re: Import Successful with warnings....ORA-01658 error.. [message #327173 is a reply to message #327170] Sat, 14 June 2008 08:23 Go to previous messageGo to next message
babuknb
Messages: 1734
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
Re: Import Successful with warnings....ORA-01658 error.. [message #327177 is a reply to message #327170] Sat, 14 June 2008 09:04 Go to previous messageGo to next message
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
Re: Import Successful with warnings....ORA-01658 error.. [message #327179 is a reply to message #327153] Sat, 14 June 2008 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I previously did not notice ROWS=NO.
You will get the desired results with INGNORE=YES
Re: Import Successful with warnings....ORA-01658 error.. [message #327182 is a reply to message #327179] Sat, 14 June 2008 09:50 Go to previous messageGo to next message
babuknb
Messages: 1734
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
Re: Import Successful with warnings....ORA-01658 error.. [message #327189 is a reply to message #327177] Sat, 14 June 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
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 Go to previous messageGo to next message
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>

Re: Import Successful with warnings....ORA-01658 error.. [message #327223 is a reply to message #327221] Sun, 15 June 2008 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
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
Re: Import Successful with warnings....ORA-01658 error.. [message #327225 is a reply to message #327223] Sun, 15 June 2008 07:51 Go to previous messageGo to next message
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".


Re: Import Successful with warnings....ORA-01658 error.. [message #327236 is a reply to message #327225] Sun, 15 June 2008 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
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
Re: Import Successful with warnings....ORA-01658 error.. [message #327376 is a reply to message #327236] Mon, 16 June 2008 04:42 Go to previous message
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:
  


Current Time: Fri Dec 02 14:02:48 CST 2016

Total time taken to generate the page: 0.06630 seconds