Export/Import

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.

goyal_vishalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

catchmeifuwantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
grim_toasterCommented:
Or alternatively actually create the tablespace "WORK" prior to your import.
0
seazodiacCommented:
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
seazodiacCommented:
Another alternative is that You can create the exactly same tablespace WORK in the target database.

0
yingkuanCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.