Location: Oracle export and import

Discussion: EXP & IMP only restores to same tablespace(s)Reported This is a featured thread

Category: Discussion Forum / General Discussion
Showing 4 posts
osde8info
osde8info
EXP & IMP only restores to same tablespace(s)
Mar 12 2008, 8:12 AM EDT | Post edited: Mar 12 2008, 8:12 AM EDT
so if you want to restore/move tables to new tablespaces you must do this manually afterwards :

CREATE SMALLFILE TABLESPACE "USERS_REORG0" DATAFILE '/u1/app/mydb/users01_reorg0.dbf' SIZE 20M
REUSE AUTOEXTEND ON NEXT 2M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

ALTER TABLE "SCOTT"."DEPT" MOVE TABLESPACE "USERS_REORG0"
ALTER INDEX "SCOTT"."PK_DEPT" REBUILD TABLESPACE "USERS_REORG0"

ALTER TABLE "SCOTT"."EMP" MOVE TABLESPACE "USERS_REORG0"
ALTER INDEX "SCOTT"."PK_EMP" REBUILD TABLESPACE "USERS_REORG0"

etc
etc
 22  out of 24 found this valuable. Do you?    

smellinger
1. RE: EXP & IMP only restores to same tablespace(s)
Mar 16 2008, 9:46 AM EDT | Post edited: Mar 16 2008, 9:46 AM EDT
Actually there is a way to do this through the import command. If you want to move your data to another tablespace after doing an export all you need to do are these steps in your import database 1) modify the default tablespace for the user(s) to the tablespace you would like it to be. 2) alter the user quota 0 (zero) on the old data and index tablespace(s). 3) import the data.

what ends up happening is that the import will look to use the tablespace in the import file but then it realizes it does not have access to them. It will then move both the TABLES and INDEXES to the default tablespace.

Having said that, if you want your indexes in the same tablespace as your tables there is nothing to do. If you dont, you will have to move them manually. 2  out of 3 found this valuable. Do you?    

JohnKK
2. RE: EXP & IMP only restores to same tablespace(s)
Apr 21 2009, 11:52 PM EDT | Post edited: Apr 21 2009, 11:52 PM EDT
why not try Navicat gui? It is easy to work on import/export. Do you find this valuable?    
luciofer
luciofer
3. RE: EXP & IMP only restores to same tablespace(s)
Apr 24 2009, 8:59 AM EDT | Post edited: Apr 24 2009, 8:59 AM EDT
"why not try Navicat gui? It is easy to work on import/export."
Why not TOAD or Embarcadero?
I guess we are not talking about commercial tools here, but plain sqlplus commands that every good DBA should know.

Cheers Do you find this valuable?    

Related Content

  (what's this?Related ContentThanks to keyword tags, links to related pages and threads are added to the bottom of your pages. Up to 15 links are shown, determined by matching tags and by how recently the content was updated; keeping the most current at the top. Share your feedback on Wetpaint Central.)
Data Pump.....and Compression? Oracle export and import Expdp Client side ?
Data Import and Export