Re: Table belongs to Original tablespace after export with DataPump
Date: Fri, 30 Jan 2009 10:45:42 +0100
Message-Id: <1233308742.6875.19.camel_at_nebuchaddnezzar>
El vie, 30-01-2009 a las 10:15 +0800, Amir Gheibi escribió:
> Thanks for the reply. But my question is how can I prevent this while
> importing so I don't have to run another script after that.
If you have created both tablespaces in the destination and the imported owner as grant to use the original tablespace, i think that it's difficult to force the object creation in the new tablespace.
If you're able, in the destination system deny access to the original tablespace, be sure that the new schema as defined the default tablespace to the new one.
Import the schema data with the FROM USER TO USER parameters
With this, if the user has no access to the tablespace defined in the DDL sentence, the table/index will be created in the default tablespace
After that, modify default attributes of the imported tables/indexes to point to the default tablespace ( this is mandatory, if you don't do it, any DDL created extracting data from the dictionary - another export, dbms_metadata.get_ddl... will create it with the original tablespace info, not with the info about where is the object stored now ).
If you have separate tablespace for indexes, you will need to rebuild them pointing to the new tablespace.
This applies to exp/imp method, if the original database and destination one is 10g you can use datapump and iirc you can inform a new tablespace for the imported objects (I you can use datapump from destination if it's 10g with 9i in the original database, using dblink from 10g DB )
HTH Regards
D.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 30 2009 - 03:45:42 CST