?
Solved

Export/Import

Posted on 2003-12-08
8
Medium Priority
?
1,927 Views
Last Modified: 2012-06-27
I have 5 tables in a schema in a database with default tablespace name 'WORK'. out of which one table 'MOLECULE' contains a CLOB Field. i export this schema(VISHAL) using following command

exp vishal/vishal file = c:\test

Now i want to import it in other database where the same user 'VISHAL' exists but with default tablespace 'DATA' and there is no tablespace with name 'WORK' as it was in former database.
Now when i import it with IGNORE = Y, the problem is that 4 tables are imported successfully, but 5th table 'MOLECULE' gives error that tablespace 'WORK' does not exist.

I show you the log file :


Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. . importing table                        "ASSAY"          0 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "MOLECULE" ("MOLECULE_ID" NUMBER(6, 0) NOT NULL ENABLE, "MOLEC"
 "ULE_2D_STRUCTURE" CLOB, "MOLECULE_WEIGHT" NUMBER, "MOLECULAR_FORMULA" VARCH"
 "AR2(25))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INI"
 "TIAL 29900800 NEXT 15925248 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE "
 "50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WORK" LOB"
 " ("MOLECULE_2D_STRUCTURE") STORE AS  (TABLESPACE "WORK" ENABLE STORAGE IN R"
 "OW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 40960 NEXT 40960 MINEX"
 "TENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 "
 "BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'WORK' does not exist
. . importing table         "MOLECULE_INFORMATION"          0 rows imported
. . importing table                    "REFERENCE"          0 rows imported
. . importing table            "SYS_SYSTEM_LOGONS"         81 rows imported
. . importing table                "PRIMARYTARGET"          0 rows imported
About to enable constraints...
Import terminated successfully with warnings.

Is it because of CLOB Field????
can anyone help me please.

0
Comment
Question by:goyal_vishal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 128 total points
ID: 9895499
This could be because other 4 tables were created without the tablespace clause and the 5th one with tablespace work clause.

Before importing create the Table and then use ignore=y

CREATE TABLE MOLECULE
blah...blah blah..
)
Tablespace Data;

import the data for the 5th table alone with ignore =y

imp user/pwd file=<file> tables=MOLECULE ignore=y

--------------

HTH

0
 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 124 total points
ID: 9895922
Or alternatively actually create the tablespace "WORK" prior to your import.
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 124 total points
ID: 9896091
Yes, it's because of CLOB field.

The real reason is because All the tables WITH at least one LOB fields CANNOT be created by default IMP process.
the correct way to do this is create the 'MOLECULE' table in the target database first, then import the all the tables with the parameter 'IGNORE=Y' at the import line:
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9896204
Another alternative is that You can create the exactly same tablespace WORK in the target database.

0
 

Assisted Solution

by:yingkuan
yingkuan earned 124 total points
ID: 12087768
This is a known issue when trying to import a table that contains LOB segments, which are stored out of line of the table. The out of line LOB segments will be imported into the original tablespace. If that tablespace is not found, then the ORA-959 error is raised. This should be fixed by Oracle Corp. in the future, but there is no known fix date or fix version at this time.

To get around this issue, you have one of two solutions. One, create tablespace 'XXX' and perform the import. When done, move the segments to the appropriate new tablespace and drop tablespace 'XXX'. Two, run import with SHOW=Y and LOG=some_log_file. This will spool out any DDL statements in the import file. You now have the DDL statements required to precreate your tables and LOB segments. Modify this file to create these segments in the appropriate tablespace. Then, run the import again with IGNORE=Y to import the data.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question