?
Solved

import error.

Posted on 2005-03-01
4
Medium Priority
?
1,286 Views
Last Modified: 2008-02-01
I am getting the following error when attempting to import to an oracle 8.1.7 database...  Please tell me what to do.


IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "BOM" ("RID" NUMBER(38, 0), "NAME" VARCHAR2(100) NOT NULL ENAB"
 "LE, "REVISION" NUMBER(38, 0), "BOM_DATA" BLOB, "REMARKS" VARCHAR2(250), "OB"
 "SOLETE" VARCHAR2(1), "PERMANENT" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRA"
 "NS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 122880 NEXT 106496 MINEXTENTS 1 M"
 "AXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
 "L DEFAULT) TABLESPACE "SA_DATA1" LOB ("BOM_DATA") STORE AS  (TABLESPACE "SA"
 "_DATA1" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(IN"
 "ITIAL 106496 NEXT 122880 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 F"
 "REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'SA_DATA1' does not exist
0
Comment
Question by:Chuck_aa
4 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13434710
Here are 2 options:

1) create tablespace SA_DATA1

2) create the table BOM manually in the tablespace you want it in
    import the dmp file using the indexfile= option
    edit the indexfile, remove remarks and correct the tablespace
    import the table with the IGNORE=Y option
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 13436482
The problem with LOBS is that it has storage clause accompanying it in the export dump. So if the specified tablespace does not exist the import of that table will fail. So you need to Manually create the table first with the below statement and than run the import. Dont forget to mention IGNORE=Y as that will ignore the table already exist error and will load the data into that table.

CREATE TABLE BOM (RID NUMBER(38, 0),
NAME VARCHAR2(100) NOT NULL ENABLE,
REVISION NUMBER(38, 0),
BOM_DATA BLOB,
REMARKS VARCHAR2(250),
OBSOLETE VARCHAR2(1),
PERMANENT VARCHAR2(1))  

Hope this helps.
0
 
LVL 11

Accepted Solution

by:
sujit_kumar earned 2000 total points
ID: 13436558
Either create the TableSpace 'SA_DATA1' then Import. Otherwise do a Full export (full=yes) and Import using ignore=Y option.
0
 
LVL 1

Expert Comment

by:fengq
ID: 13444266
you can use editor to edit your dmp file to find out what user tablespaces are need. make sure create them and drop all the non system users before you do the import.
0

Featured Post

Technology Partners: 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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

621 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