?
Solved

import error.

Posted on 2005-03-01
4
Medium Priority
?
1,279 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
[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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 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