Solved

Export/Import

Posted on 2003-12-08
8
1,923 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
8 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 32 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 31 total points
ID: 9895922
Or alternatively actually create the tablespace "WORK" prior to your import.
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 31 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 31 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

808 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