Solved

Export/Import

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now