Home » RDBMS Server » Server Utilities » IMP-00017,IMP-00003 with ora-00959 (10gR2, 10.2.0.3,Win2k3)
IMP-00017,IMP-00003 with ora-00959 [message #362863] Thu, 04 December 2008 06:07 Go to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Hello,

After a successful export of a schema, I got a warning while doing an import of the schema I had just exported.

I checked the log and saw the following errors:
IMP-00017 : following statement failed with ORACLE error 959:
 "CREATE TABLE "M4DMS_ORA_DOC_CO1" ("ID_DOC" NUMBER(8, 0), "ID_DOC_VERSION" N"
 "UMBER(2, 0), "DOC_CONTENT" BLOB, "ISO_LANGUAGE" VARCHAR2(3), "DOC_FORMAT_TY"
 "PE" VARCHAR2(10), "ID_CHAR_SET" VARCHAR2(20))  PCTFREE 1 PCTUSED 40 INITRAN"
 "S 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER"
 "_POOL DEFAULT) TABLESPACE "M4PRO" LOGGING NOCOMPRESS LOB ("DOC_CONTENT") ST"
 "ORE AS  (TABLESPACE "M4PRO" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 "
 "NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER"
 "_POOL DEFAULT))"
IMP-00003: Erreur ORACLE 959 rencontrée
ORA-00959: Tablespace 'M4PRO' does not exit


To make sure the 'guilty' tablespace does indeed exist, I ran the following as sysdba:
SQL> conn /@m4client as sysdba
ConnectÚ.
SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'M%'
;


and the result is as follows:
TABLESPACE_NAME
------------------------------
META4
M4PRO

SQL>
Which means that the tablespace M4PRO does exit.

Why do I have that error and how can I fix it?

I am using oracle 10gR2 on both machine (The one I did the export from and the one I am trying to import into)


Thanks in advance.

[Updated on: Thu, 04 December 2008 06:25] by Moderator

Report message to a moderator

Re: IMP-00017,IMP-00003 with ora-00959 [message #362872 is a reply to message #362863] Thu, 04 December 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select '"'||tablespace_name||'"' from dba_tablespaces where tablespace_name like 'M%';

Regards
Michel
Re: IMP-00017,IMP-00003 with ora-00959 [message #362898 is a reply to message #362872] Thu, 04 December 2008 07:45 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Here:
SQL> select '"'||tablespace_name||'"' from dba_tablespaces where tablespace_name
 like 'M%';

Result:
'"'||TABLESPACE_NAME||'"'
--------------------------------
"META4"
"M4PRO"

here's the command I used for the import:
imp system/psswd fromuser=m4dev touser=m4prod log=path_to_logfile file=path_to_dump_file.dmp grants=n commit=y statistics=recalculate.


When I ran a select table_name on dba_tables on the database I exported the schema from, I found that the table which seems to cause problem does also exist. Any tips to help me go on?

Thanks in advance.
Re: IMP-00017,IMP-00003 with ora-00959 [message #362909 is a reply to message #362863] Thu, 04 December 2008 08:26 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, your tablespace exists in the database where you exported, but it doesn't exist in the database where you are importing.
Re: IMP-00017,IMP-00003 with ora-00959 [message #362913 is a reply to message #362909] Thu, 04 December 2008 08:53 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
joy_division wrote on Thu, 04 December 2008 08:26
Yes, your tablespace exists in the database where you exported, but it doesn't exist in the database where you are importing.


Uhmm... to check what you said, joy_div, I ran the following on the database where I am importing:
SQL> select username,default_tablespace from dba_users where username like 'M4%';


USERNAME                       DEFAULT_TABLESPACE
------------------------------ -----------------------------------------
M4PROD                       M4PROD

The default tablespace of the user I am importing into is M4PROD, not M4PRO. So should I create a tablespace M4PRO in the database I am importing, then alter the user M4PROD and set his default tablespace to M4PRO? If no, how should I proceed?

Thanks in advance?
Re: IMP-00017,IMP-00003 with ora-00959 [message #362979 is a reply to message #362913] Thu, 04 December 2008 13:28 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Try This

1/ Connect target databaes (where your going to import)

2/ Conn sys or System

3/
Revoke resource from m4prod;

4/ Try import again (if you want include IGNORE=Y )

5/ Let us know

Babu
Re: IMP-00017,IMP-00003 with ora-00959 [message #363011 is a reply to message #362913] Thu, 04 December 2008 15:30 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
abdulaziz wrote on Thu, 04 December 2008 09:53

The default tablespace of the user I am importing into is M4PROD, not M4PRO. So should I create a tablespace M4PRO in the database I am importing, then alter the user M4PROD and set his default tablespace to M4PRO?



I would say that's the best option unless you don't have the space for all the objects that are going to go into M$PRO.

p.s. Usually I don't like saying things like this, but please do not follow the advice of Babu as it is incorrect.
Re: IMP-00017,IMP-00003 with ora-00959 [message #363021 is a reply to message #363011] Thu, 04 December 2008 16:17 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello Joy,
Quote:

>>p.s. Usually I don't like saying things like this, but please do not follow the advice of Babu as it is incorrect


Thanks for your comments.

Please find my log & let me know your comments.


SQL> conn babu
Enter password:
Connected.
SQL> select username,default_tablespace from dba_users where username='BABU';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
BABU                           BABU01

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
A                              TABLE
M_EMP                          TABLE
M                              TABLE
MLOG$_M                        TABLE
RUPD$_M                        TABLE
M_1                            TABLE
USLOG$_M_1                     TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
M_2                            TABLE
USLOG$_M_2                     TABLE
M_3                            TABLE
USLOG$_M_3                     TABLE
M_4                            TABLE
MLOG$_A                        TABLE
MV_A                           TABLE

18 rows selected.

SQL> create table babu_test (a int);

Table created.

SQL> ! exp babu/babu file=babu.dmp tables=babu_test

Export: Release 10.2.0.3.0 - Production on Thu Dec 4 22:02:47 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                      BABU_TEST          0 rows exported
Export terminated successfully without warnings.


SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> alter user test quota unlimited on users;

User altered.

SQL> grant resource to test; -----> Role -> Granted

Grant succeeded.

SQL> ! imp babu/babu file=babu.dmp fromuser=babu touser=test

Import: Release 10.2.0.3.0 - Production on Thu Dec 4 22:10:45 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing BABU's objects into TEST
. . importing table                    "BABU_TEST"          0 rows imported
Import terminated successfully without warnings.


SQL> col SEGMENT_NAME format a20
SQL> col TABLESPACE_NAME format a20
SQL> l
  1* select owner,SEGMENT_NAME,TABLESPACE_NAME from dba_segments where SEGMENT_NAME='BABU_TEST'
SQL> /

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- --------------------
BABU                           BABU_TEST            BABU01
TEST                           BABU_TEST            BABU01


SQL> conn test/test
Connected.
SQL> drop table BABU_TEST;

Table dropped.

SQL> conn babu/babu
Connected.

SQL> revoke resource from test;   ---> Role -> Revoked

Revoke succeeded.

SQL> ! imp babu/babu file=babu.dmp fromuser=babu touser=test

Import: Release 10.2.0.3.0 - Production on Thu Dec 4 22:12:47 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing BABU's objects into TEST
. . importing table                    "BABU_TEST"          0 rows imported
Import terminated successfully without warnings.

SQL> select owner,SEGMENT_NAME,TABLESPACE_NAME from dba_segments where SEGMENT_NAME='BABU_TEST'
  2  /

OWNER                          SEGMENT_NAME         TABLESPACE_NAME
------------------------------ -------------------- --------------------
BABU                           BABU_TEST            BABU01
TEST                           BABU_TEST            USERS



Babu

[Updated on: Thu, 04 December 2008 16:20]

Report message to a moderator

Re: IMP-00017,IMP-00003 with ora-00959 [message #363109 is a reply to message #363021] Fri, 05 December 2008 02:27 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Hello Joy, Hello Bab,

Thanks for all your tips and comments. If there is one thing I learned from this experience, it's that, it's better to create a tablespace with same name as the tablespace the dump file comes from, into the database where the import is to be done.
Here's what I did yesterday : I created a tablespace M4PRO in the database I was importing into, then I created a new user with his default tablespace sets to M4PRO, then I ran the import command and it was successful with no warnings.

Now I know exactely how to proceed in production. However, I think it's good to test new things and see how they work; That's why I'll follow both your tips and let you guys know how it went.

See you in a while.
Re: IMP-00017,IMP-00003 with ora-00959 [message #363165 is a reply to message #363109] Fri, 05 December 2008 04:53 Go to previous messageGo to next message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
Hello,

Bab, I tried your thing but I got the
IMP-00019: row rejected due to oracle error 1
ORA-00001: Unique constraint violated.

There's something else I noticed though: I re-did the exact same operation that lead me to opening this thread and this time I got no warnings. Likewise, during the my first import, when I checked the log after I realised that the import was terminated successfully with warnings, I discovered that on the over 2500 tables contain in the dump file, the error was thrown for only one table. Seemed to me that the remaining tables were imported just fine. Does anyone have an explanation for such a weird behavior?

Anyway in the future(wherever I could do the check), I'll make sure a tablespace with the same name and characteristics does exist.
Re: IMP-00017,IMP-00003 with ora-00959 [message #363235 is a reply to message #363165] Fri, 05 December 2008 08:17 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
abdulaziz wrote on Fri, 05 December 2008 05:53
successfully with warnings, I discovered that on the over 2500 tables contain in the dump file, the error was thrown for only one table.



That table had a LOB datatype. It's probably more restrictive in that it won't use the default tablespace if the named tablespace doesn't exist. Like you said, make sure all tablespaces exist prior to import first.
Re: IMP-00017,IMP-00003 with ora-00959 [message #363260 is a reply to message #363235] Fri, 05 December 2008 10:24 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:

t's that, it's better to create a tablespace with same name as the tablespace the dump file comes from, into the database where


Ok. I agree but why your not trying to revoke 'RESOURCE' role??

Quote:
Bab, I tried your thing but I got the
IMP-00019: row rejected due to oracle error 1
ORA-00001: Unique constraint violated.


Try this

1/ During import try this;

imp ... fromuser=a touser=b rows=n ignore=y


ps: make sure before import if you have any LOB column; pls manually create tables
select * from user_lobs;


2. Disable all constriants; (try to write one custom script)

3. Again import with data using.

imp.. fromuser=a touser=b ignore=y rows=y constraints=n indexes=n

4. Enable all constraints; (try to write one custom script)

Thank you in advance.

Babu



Re: IMP-00017,IMP-00003 with ora-00959 [message #364582 is a reply to message #363260] Tue, 09 December 2008 03:32 Go to previous message
abdulaziz
Messages: 102
Registered: May 2008
Location: Douala
Senior Member
[quote title=gentlebabu wrote on Fri, 05 December 2008 10:24]
Quote:
Try this

1/ During import try this;

imp ... fromuser=a touser=b rows=n ignore=y


ps: make sure before import if you have any LOB column; pls manually create tables
select * from user_lobs;


2. Disable all constriants; (try to write one custom script)

3. Again import with data using.

imp.. fromuser=a touser=b ignore=y rows=y constraints=n indexes=n

4. Enable all constraints; (try to write one custom script)

Thank you in advance.

Babu.



Ok.
Previous Topic: exp user in oracle 8i and import it in oracle 10g
Next Topic: SQL Loader via Concurrent Program...response?
Goto Forum:
  


Current Time: Mon Dec 23 20:28:10 CST 2024