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  |
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>
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 #362898 is a reply to message #362872] |
Thu, 04 December 2008 07:45  |
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 #362913 is a reply to message #362909] |
Thu, 04 December 2008 08:53 |
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 #363011 is a reply to message #362913] |
Thu, 04 December 2008 15:30 |
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 |
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 |
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 |
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 |
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 #364582 is a reply to message #363260] |
Tue, 09 December 2008 03:32 |
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
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.
|
|
|
|
Goto Forum:
Current Time: Fri May 03 20:48:22 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
