Oracle Error 959 Import
Contents |
Last updated: February 25, 2012 - 5:04 am UTC Category: Database � Version: 9.2.0.7 Whilst you are here, check out some content from the AskTom team: Table Functions, ora-00959 tablespace does not exist during import Part 3b: implementing table functions for PL/SQL Challenge reports Latest Followup You ora-00959 tablespace does not exist during impdp Asked I am trying to move objects from one tablespace to another tablespace using export and import. Is there how to import tablespace in oracle 11g any way to avoid the errors below? ORA-01950: no privileges on tablespace 'USERS' Thank you for your time. C:\>exp userid=imptest/imp file=exp.dmp Export: Release 9.2.0.7.0 - Production on Mon Jun 26
Imp-00017: Following Statement Failed With Oracle Error 942:
14:09:39 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set server uses US7ASCII character set (possible charset conversion) . exporting pre-schema procedural objects and actions how to create tablespace in oracle 11g . exporting foreign function library names for user IMPTEST . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user IMPTEST About to export IMPTEST's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export IMPTEST's tables via Conventional Path ... . . exporting table DEMO_IOT 6091 rows exported . . exporting table MYDBAOBJECTS 0 rows exported . . exporting table NEWS_TEST 0 rows exported . . exporting table T 100 rows exported . . exporting table TESTUNDO 0 rows exported . . exporting table TT 0 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes EXP-00091: Exporting questionable statistics. . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . e
to a different tablespace If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To http://www.dbasupport.com/forums/showthread.php?35032-importing-to-a-different-tablespace start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 8 of 8 Thread: importing to a different tablespace Tweet Thread Tools Show Printable Version Email this Page… Subscribe http://dgielis.blogspot.com/2006/05/oracle-export-import-problem-with.html to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 03-28-2003,08:55 AM #1 Horace View Profile View Forum Posts Yeah, I Know....... Join Date Dec 1999 Location Purgatory Posts 346 importing to a oracle error different tablespace Hi guys, I specifically exported a large partitioned table. The tablespace it was in is TABSPACE1, which also contains many more large tables When I import into another instance, which does not have the tablespace TABSPACE1, I get the error message IMP-00003: ORACLE error 959 encountered ORA-00959: tablespace 'TABSPACE1' does not exist Import terminated successfully with warnings. This is fair enough, but is there a way of forcing the import into another ora-00959 tablespace does tablespace ?? I don't particularly want to create a new tablespace, and I don't want to transport the whole TABSPACE1 tablespace. Your thoughts would be appreciated. Reply With Quote 03-28-2003,09:17 AM #2 gandolf989 View Profile View Forum Posts Visit Homepage mod Join Date Nov 2000 Location Pittsburgh, PA Posts 4,124 If you specify ignore=y in your import the table will get created in the schema's default tablespace. Just make sure that the default tablespace is the one that you want to use. Otherwise create the table the way you want in the proper tablespace then import with ignore=y. Reply With Quote 03-29-2003,03:01 AM #3 nagarjuna View Profile View Forum Posts Oracle Apps DBA Join Date Aug 2002 Location Bangalore, India Posts 405 It's a problem with quota. Just check you have quota on your default tablespace. gandolf : No need for ignore=y when importing to default default tablespace. Oracle automatically imports to default tablespace if the tablespace defined with export dumpfile is not present in targer database. -nagarjuna Reply With Quote 03-29-2003,03:25 AM #4 julian View Profile View Forum Posts Senior Advisor Join Date Jun 2001 Location Helsinki. Finland Posts 3,938 Originally posted by nagarjuna It's a problem with quota. Just check you have quota on your default tablespace. gandolf : No need for ignore=y when importing to default default tables
Import problem with CLOB's I'm currently reorganising some databases…As I do it for the x time, I'm working on "auto-pilot".But suddenly I received an error:--IMP-00003: ORACLE error 959 encounteredORA-00959: tablespace 'TOOLS_DATA' does not existIMP-00017: following statement failed with ORACLE error 959:"CREATE TABLE "D_JOB" ("O_ID" NUMBER(10, 0), "D_TITLE" VARCHAR2(250), "D_OUTPUT" CLOB, "D_PERCENTAGE" NUMBER(10, 0), "D_LASTUPDATE" NUMBER(10, 0), "D_OWNER" NUMBER(10, 0), "D_STATUS" VARCHAR2(20), "D_PRIORITY" NUMBER(10, 0), "D_TYPE" VARCHAR2(20), "D_TIME" VARCHAR2(5), "D_DATE" DATE, "D_CLASS" VARCHAR2(250)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOOLS_DATA" LOB ("D_OUTPUT") STORE AS (TABLESPACE "TOOLS_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))" --What I did:create a full export of the source databasecreate all the necessary users, roles and tablespaces on the target database(use of naming conventions, from dictionary to locally managed tablespace, change of parameters etc.)import from -> to user on the target database(Actually I'm doing the import twice, as the database characterset changed. I create first the structure without data, afterwards change the tables char columns from byte to char and finish with the import of the data)Almost all tables were imported fine, but the tables with some CLOB's in weren't!As I changed the default tablespace of the user (and the old tablespace didn't exist anymore) the import couldn't find the tablespace and gave the above error.I suppose I forgot that if you've tables with clobs in, the default tablespace of the user isn't automatically taken… to solve the problem, I had to recreate the tables manually with the right storage clause (tablespace).I learn a new thing every day (although I think for this, I just forgot this behaviour of Oracle)! ;-) Posted by Dimitri Gielis at 15:41 2 comments: kurt said... This is also true for blobs (of course) and partitions ;-)Actually anything which can have a second storage clause in a lower lexical level ... 6/10/2006 2:06 PM soma said... I am also facing the same problem while exporting and importing data from development to production database 7/27/2007 4:39 PM Post a C